package com.ccp.dev.form.service;

import com.ccp.dev.core.basic.base.BaseService;
import com.ccp.dev.core.basic.db.datasource.DbContextHolder;
import com.ccp.dev.core.basic.db.datasource.JdbcTemplateUtil;
import com.ccp.dev.core.basic.engine.FreemarkEngine;
import com.ccp.dev.core.basic.engine.GroovyScriptEngine;
import com.ccp.dev.core.basic.page.PageList;
import com.ccp.dev.core.basic.table.ColumnModel;
import com.ccp.dev.core.basic.table.TableModel;
import com.ccp.dev.core.basic.util.ContextUtil;
import com.ccp.dev.core.basic.util.UUIDUtils;
import com.ccp.dev.core.poi.excel.Excel;
import com.ccp.dev.core.poi.excel.editor.IFontEditor;
import com.ccp.dev.core.poi.excel.reader.DataEntity;
import com.ccp.dev.core.poi.excel.reader.ExcelReader;
import com.ccp.dev.core.poi.excel.reader.FieldEntity;
import com.ccp.dev.core.poi.excel.reader.TableEntity;
import com.ccp.dev.core.poi.excel.style.Color;
import com.ccp.dev.core.poi.excel.style.font.BoldWeight;
import com.ccp.dev.core.poi.excel.style.font.Font;
import com.ccp.dev.core.util.*;
import com.ccp.dev.form.consts.FormConstants;
import com.ccp.dev.form.dao.DataTemplateDao;
import com.ccp.dev.form.dao.FormDefDao;
import com.ccp.dev.form.dao.FormTemplateDao;
import com.ccp.dev.form.model.*;
import com.ccp.dev.form.service.bus.QueryUtil;
import com.ccp.dev.form.util.CommonVar;
import com.ccp.dev.form.util.FieldPool;
import com.ccp.dev.system.model.Position;
import com.ccp.dev.system.model.SysOrg;
import com.ccp.dev.system.model.SysRole;
import com.ccp.dev.system.service.DictionaryService;
import com.ccp.dev.system.service.PositionService;
import com.ccp.dev.system.service.SysOrgService;
import com.ccp.dev.system.service.SysRoleService;
import com.ccp.dev.workflow.bpmutil.FormDataUtil;
import com.ccp.dev.workflow.bpmutil.ServiceUtil;
import com.ccp.dev.workflow.model.ProcessRun;
import com.ccp.dev.workflow.model.bpm.BpmBusLink;
import com.ccp.dev.workflow.service.FormHandlerService;
import com.ccp.dev.core.db.entity.SQLClause;
import com.ccp.dev.workflow.service.ProcessRunService;
import net.sf.ezmorph.object.DateMorpher;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.util.JSONUtils;
import org.apache.commons.collections.map.CaseInsensitiveMap;
import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.text.StringEscapeUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.*;
import com.ccp.dev.system.model.Dictionary;

/**
 * BPM_DATA_TEMPLATE service类
 *
 * @author zhaowj
 */
@Service
public class DataTemplateService extends BaseService<DataTemplate> {

    private static final Log logger = LogFactory.getLog(DataTemplateService.class);

    @Resource
    private DataTemplateDao dataTemplateDao;

    @Resource
    private FormTemplateDao formTemplateDao;

    @Resource
    private FormDefDao formDefDao;

    @Resource
    private FormTableService formTableService;

    @Resource
    private FormFieldService formFieldService;

    @Resource
    private DictionaryService dictionaryService;

    @Resource
    private FreemarkEngine freemarkEngine;

    @Resource
    private SysRoleService sysRoleService;

    @Resource
    private PositionService positionService;

    @Resource
    private SysOrgService sysOrgService;

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private GroovyScriptEngine groovyScriptEngine;

    @Resource
    private JdbcTemplateUtil jdbcTemplateUtil;

    @Resource
    private FormDefService formDefService;

    @Resource
    private FormHandlerService formHandlerService;

    @Resource
    private ProcessRunService processRunService;


    /**
     * 获取 数据模板 根据表单key
     *
     * @param formKey 表单key
     * @return DataTemplate
     */
    public DataTemplate getByFormKey(String formKey) {
        return dataTemplateDao.getByFormKey(formKey);
    }


    /**
     * 编辑时候解密主子表的数据
     *
     * @param data    表单数据
     * @param tableId 表ID
     * @param key     解密，加密标识
     * @throws Exception
     */
    public void decryptMainSubFieldMap(FormData data, String tableId, int key) throws Exception {
        //解密主表数据
        List<FormField> encryptList = formFieldService.getEncryptFieldByTableId(tableId);
        encryptDecryptMap(data.getMainFields(), encryptList, key, false);
        //查询所有子表的数据
        List<SubTable> subTables = data.getSubTableList();
        if (subTables.size() > 0) {
            List<FormField> subEncryptList = getEncryptSubList(tableId);
            for (SubTable subTable : subTables) {
                List<Map<String, Object>> subTableDataList = subTable.getDataList();
                if (subTableDataList.size() > 0) {
                    decryptEncryptFileds(subTableDataList, subEncryptList, key);
                }
            }
        }
    }

    /**
     * 解密加密通用方法
     *
     * @param map         数据
     * @param encryptList 加密字段集合
     * @param key         加密解密标识
     * @param isKeyToUp   是否key需要转成大写字母
     * @throws Exception
     */
    public void encryptDecryptMap(Map<String, Object> map, List<FormField> encryptList, int key, boolean isKeyToUp) throws Exception {
        for (FormField field : encryptList) {
            String decryptFieldName = field.getFieldName();
            //如果包含这个加密字段key
            if (map.containsKey(decryptFieldName) && null != map.get(decryptFieldName) && !"".equals(decryptFieldName)) {
                String classStr;
                //加密类
                if (key == FormField.ISENCRYPT) {
                    classStr = field.getEncryptClass();
                } else {//解密类
                    classStr = field.getDecryptClass();
                }
                if (StringUtil.isNotEmpty(classStr)) {
                    String[] ary = classStr.split("[.]");
                    if (ary != null) {
                        String beanId = ary[0];
                        String method = ary[1];
                        Object serviceBean = AppUtil.getBean(beanId);
                        if (serviceBean != null) {
                            Method invokeMethod = serviceBean.getClass().getDeclaredMethod(method, new Class[]{String.class});
                            String decryptVal = (String) invokeMethod.invoke(serviceBean, map.get(decryptFieldName).toString());
                            //放入加密/解密后的值
                            String keyName = decryptFieldName;
                            if (isKeyToUp) {
                                keyName = decryptFieldName.toUpperCase();
                            }
                            map.put(keyName, decryptVal);
                        }
                    }
                }
            }
        }
    }


    /**
     * 通过主表id获取子表所有的加密字段list
     *
     * @param tableId 表id
     * @return 加密字段集合
     */
    private List<FormField> getEncryptSubList(String tableId) {
        List<FormTable> subTableList = formTableService.getSubTableByMainTableId(tableId);
        List<FormField> subEncryptList = new ArrayList<>();
        for (FormTable sub : subTableList) {
            subEncryptList.addAll(formFieldService.getEncryptFieldByTableId(sub.getTableId()));
        }
        return subEncryptList;
    }

    /**
     * 调用解密/加密
     *
     * @param list
     * @param encryptList
     * @param key         解密，加密标识
     */
    public void decryptEncryptFileds(List<Map<String, Object>> list, List<FormField> encryptList, int key) throws Exception {
        for (Map<String, Object> map : list) {
            encryptDecryptMap(map, encryptList, key, false);
        }
    }

    /**
     * 根据formKey获取业务表单数量。
     *
     * @param formKey
     * @return
     */
    public Integer getCountByFormKey(String formKey) {
        return dataTemplateDao.getCountByFormKey(formKey);
    }

    /**
     * 取得 DataTemplate 实体
     *
     * @param request
     * @return
     */
    public DataTemplate getFormObject(HttpServletRequest request) {

        JSONUtils.getMorpherRegistry().registerMorpher(new DateMorpher(new String[]{"yyyy-MM-dd"}));

        String json = RequestUtil.getString(request, "json", false);
        json = StringEscapeUtils.unescapeHtml4(json);
        if (StringUtil.isEmpty(json)) {
            return null;
        }
        JSONObject obj = JSONObject.fromObject(json);

        String displayField = obj.getString("displayField");
        String conditionField = obj.getString("conditionField");
        String sortField = obj.getString("sortField");
        String filterField = obj.getString("filterField");
        String manageField = obj.getString("manageField");
        String exportField = obj.getString("exportField");

        obj.remove("displayField");
        obj.remove("conditionField");
        obj.remove("sortField");
        obj.remove("filterField");
        obj.remove("manageField");

        DataTemplate bpmDataTemplate = (DataTemplate) JSONObject.toBean(obj, DataTemplate.class);
        bpmDataTemplate.setDisplayField(displayField);
        bpmDataTemplate.setConditionField(conditionField);
        bpmDataTemplate.setSortField(sortField);
        bpmDataTemplate.setFilterField(filterField);
        bpmDataTemplate.setManageField(manageField);
        bpmDataTemplate.setExportField(exportField);
        return bpmDataTemplate;
    }


    /**
     * 保存信息
     *
     * @param bpmDataTemplate 业务数据模板对象
     * @throws Exception
     */
    @Transactional(rollbackFor = Exception.class)
    public void saveDataTemplate(DataTemplate bpmDataTemplate) throws Exception {
        String templateHtml = generateTemplate(bpmDataTemplate);
        //每次保存都需要重新生成模板
        bpmDataTemplate.setTemplateHtml(templateHtml);
        //防止第一次保存了业务数据模板，没有关闭窗口，继续修改其他选项卡的值，导致会入多条记录的问题
        DataTemplate formKey_bpmDataTemplate = dataTemplateDao.getByFormKey(bpmDataTemplate.getFormKey());
        if(BeanUtils.isNotEmpty(formKey_bpmDataTemplate)){
            bpmDataTemplate.setId(formKey_bpmDataTemplate.getId());
        }
        if (StringUtil.isEmpty(bpmDataTemplate.getId()) && BeanUtils.isEmpty(formKey_bpmDataTemplate)) {
            bpmDataTemplate.setId(UUIDUtils.getUUIDFor32());
            this.add(bpmDataTemplate);
        } else {
            this.update(bpmDataTemplate);
        }
    }

    /**
     * 更新业务数据模板的数据模板
     *
     * @param dataTemplate 业务数据模板对象
     * @throws Exception
     */
    @Transactional(rollbackFor = Exception.class)
    public void updateDataTemplate(DataTemplate dataTemplate) {
        String templateHtml = dataTemplate.getTemplateHtml();
        templateHtml = templateHtml.replaceAll("''", "'");
        dataTemplate.setTemplateHtml(templateHtml);
        this.update(dataTemplate);
    }

    /**
     * 解析生成第一次的模板
     *
     * @param bpmDataTemplate
     * @return
     * @throws Exception
     */
    public String generateTemplate(DataTemplate bpmDataTemplate) throws Exception {
        //获取需要第一次解释的模板
        FormTemplate bpmFormTemplate = formTemplateDao.getByTemplateAlias(bpmDataTemplate.getTemplateAlias());

        FormTable bpmFormTable = formTableService.getByTableId(bpmDataTemplate.getTableId(), FormField.FIELD_NORMAL_HIDDEN);
        // 是否有条件查询
        boolean hasCondition = hasCondition(bpmDataTemplate.getConditionField());

        // 是否有功能按钮
        boolean hasManage = hasManage(bpmDataTemplate.getManageField());

        // 第一次解析模板
        Map<String, Object> map = new HashMap<>(10);
        map.put("bpmDataTemplate", bpmDataTemplate);
        map.put("hasCondition", hasCondition);
        map.put("hasManage", hasManage);
        map.put("pkField", bpmFormTable.getPkField());
        map.put("formatData", getFormatDataMap(bpmFormTable, bpmDataTemplate.getFormKey()));

        String templateHtml = freemarkEngine.parseByStringTemplate(map,
                bpmFormTemplate.getHtml());
        return templateHtml;

    }

    /**
     * 获取格式化的数据
     *
     * @param bpmFormTable
     * @return
     */
    private Map<String, Object> getFormatDataMap(FormTable bpmFormTable, String formKey) {
        Map<String, Object> map = new HashMap<>(10);
        //字段信息
        List<FormField> fieldList = bpmFormTable.getFieldList();
        for (FormField formField : fieldList) {
            String name = formField.getFieldName();
            Short controlType = formField.getControlType();
            String fieldType = formField.getFieldType();

            // 下面那些判断主要是为了字段值不能直接获取到的，例如日期，下拉框之类需要特殊处理的字段，那些输入框直接输入的就不用
            if (FormField.DATATYPE_DATE.equals(fieldType)) {
                map.put(name, formField.getDatefmt());
            } else {
                if (controlType == FieldPool.RADIO_INPUT || controlType == FieldPool.CHECKBOX) {
                    String options = formField.getJsonOptions();
                    if (StringUtils.isEmpty(options)) {
                        continue;
                    }
                    Map<String, String> optionMap = getOptionMap(formField);
                    map.put(name, optionMap);
                } else if (controlType.shortValue() == FieldPool.SELECT_INPUT) {
                    // 单独处理下拉框，1、定义字段时定义了值，2、定义了下拉框级联的情况处理
                    // 1、处理普通情况，定义字段时的定义值
                    Map<String, String> optionMap = getOptionMap(formField);

                    // 处理下拉框级联的情况
                    this.handCascadeOpinion(formKey, name, optionMap);
                    map.put(name, optionMap);
                } else if (controlType.shortValue() == FieldPool.DICTIONARY) {
                    //数据字典情况
                    String dicName = formField.getDictType();
                    List<Dictionary> dictionaryList = dictionaryService.getByNodeKey(dicName);
                    Map<String, String> dicMap = new HashMap<>(10);
                    for (Dictionary dictionary : dictionaryList) {
                        dicMap.put(dictionary.getItemName(), dictionary.getItemName());
                    }
                    map.put(name, dicMap);
                }
            }
        }
        return map;
    }

    /**
     * 获取下拉列表map
     *
     * @param formField 字段对象
     * @return map
     */
    private Map<String, String> getOptionMap(FormField formField) {
        Map<String, String> optionMap = new LinkedHashMap<>();
        String options = formField.getJsonOptions();
        if (StringUtil.isNotEmpty(options)) {
            JSONArray array = JSONArray.fromObject(options);
            for (Object obj : array) {
                JSONObject json = (JSONObject) obj;
                String key = (String) json.get("key");
                String value = (String) json.get("value");
                optionMap.put(key, value);
            }
        }
        return optionMap;
    }

    /**
     * 处理下拉框级联的情况
     *
     * @param formKey
     * @param selectName
     * @param optionMap
     */
    private void handCascadeOpinion(String formKey, String selectName, Map<String, String> optionMap) {
        // 根据tableId获取默认发表的表单
        FormDef bpmFormDef = formDefDao.getDefaultPublishedByFormKey(formKey);
        if (BeanUtils.isEmpty(bpmFormDef)) {
            return;
        }
        String html = bpmFormDef.getHtml();
        if (StringUtil.isNotEmpty(html)) {
            Document doc = Jsoup.parse(html);
            Elements selectElement = doc.select("select");
            //判断当前的下拉框是否 是下拉框级联的，
            String sQuery;
            for (Element element : selectElement) {
                boolean hasSelectQuery = element.hasAttr("selectquery");
                if (!hasSelectQuery) {
                    continue;
                }
                String tableName;

                String curName = element.attr("name");
                if (StringUtil.isEmpty(curName)) {
                    Element parent = element.parent();
                    sQuery = parent.attr("external");
                    String external = sQuery.replace("&#39;", "\"").replace("&quot;", "\"");
                    JSONObject selectQuery = JSONObject.fromObject(external);
                    tableName = selectQuery.getString("name");

                } else {

                    String[] name = curName.split(":");
                    tableName = name[2];
                }
                if (selectName.equals(tableName)) {
                    sQuery = element.attr("selectquery");
                    String external = sQuery.replace("&#39;", "\"").replace("&quot;", "\"");
                    JSONObject selectquery = JSONObject.fromObject(external);
                    String alias = selectquery.getString("name");
                    JSONObject binding = JSONObject.fromObject(selectquery.getString("binding"));
                    String key = binding.getString("key").toLowerCase();
                    String value = binding.getString("value").toLowerCase();
                    List result = null;
                    if (BeanUtils.isNotEmpty(result)) {
                        for (int i = 0; i < result.size(); i++) {
                            Map<String, Object> tempMap = (Map<String, Object>) result.get(i);
                            optionMap.put(tempMap.get(key).toString(), tempMap.get(value).toString());
                        }
                    }

                }
            }
        }
    }

    /**
     * 是否有条件
     *
     * @param conditionField
     * @return
     */
    private boolean hasCondition(String conditionField) {
        if (StringUtils.isEmpty(conditionField)) {
            return false;
        }
        JSONArray jsonAry = JSONArray.fromObject(conditionField);
        return jsonAry.size() > 0;
    }

    /**
     * 是否有管理
     *
     * @param manageField
     * @return
     */
    private boolean hasManage(String manageField) {
        if (StringUtils.isEmpty(manageField)) {
            return false;
        }
        JSONArray jsonAry = JSONArray.fromObject(manageField);
        return jsonAry.size() > 0;
    }

    /**
     * 获取字段列表
     *
     * @param tableId 表id
     * @return FormTable
     */
    public FormTable getFieldListByTableId(String tableId) {
        FormTable bpmFormTable = formTableService.getTableByIdContainHidden(tableId);

        List<FormTable> otherTableList = new ArrayList<>();
        // 加入BPM_BUS_LINK的表和字段
        List<FormField> fieldList = new ArrayList<>();
        FormField field0 = this.newBpmFormField("BUS_CREATOR_ID", "发起人ID",
                FormField.DATATYPE_NUMBER, FieldPool.SELECTOR_USER_SINGLE, FormField.HIDDEN);
        FormField field1 = this.newBpmFormField("BUS_ORG_ID", "发起组织ID",
                FormField.DATATYPE_NUMBER, FieldPool.SELECTOR_ORG_SINGLE, FormField.HIDDEN);
        FormField field2 = this.newBpmFormField("BUS_CREATETIME", "发起时间",
                FormField.DATATYPE_DATE, FieldPool.DATEPICKER, FormField.NO_HIDDEN);
        FormField field3 = this.newBpmFormField("BUS_UPDID", "更新人ID",
                FormField.DATATYPE_NUMBER, FieldPool.SELECTOR_USER_SINGLE, FormField.HIDDEN);
        FormField field4 = this.newBpmFormField("BUS_UPDTIME", "更新时间",
                FormField.DATATYPE_DATE, FieldPool.DATEPICKER, FormField.NO_HIDDEN);
        FormField field5 = this.newBpmFormField("BUS_STATUS", "状态",
                FormField.DATATYPE_NUMBER, FieldPool.TEXT_INPUT, FormField.VALUE_FROM_FORM);

        fieldList.add(field0);
        fieldList.add(field1);
        fieldList.add(field2);
        fieldList.add(field3);
        fieldList.add(field4);
        fieldList.add(field5);
        FormTable otherTable = new FormTable();
        otherTable.setTableName(DataTemplate.BUS_TABLE);
        otherTable.setTableDesc("业务数据关联表");
        // 标记为特殊表
        otherTable.setIsMain(FormTable.IS_BUSINESS_DATA_ASSOCIATION);
        otherTable.setIsExternal(FormTable.EXTERNAL);
        otherTable.setRelation(
                bpmFormTable.getKeyDataType().shortValue() == FormTable.PKTYPE_STRING.shortValue()
                        ? DataTemplate.BUS_TABLE_PK_STR : DataTemplate.BUS_TABLE_PK
        );

        otherTable.setFieldList(fieldList);
        otherTableList.add(otherTable);
        // end 加入BPM_BUS_LINK的表和字段
        if (BeanUtils.isNotEmpty(otherTableList)) {
            bpmFormTable.setOtherTableList(otherTableList);
        }
        return bpmFormTable;
    }

    /**
     * 返回一个FormField 对象来存放字段所对应的数据（设置参数）
     *
     * @param fieldName   字段名
     * @param fieldDesc   字段注释
     * @param fieldType   字段类型
     * @param controlType 控件类型
     * @param isHidden    是否隐藏
     * @return FormField
     */
    private FormField newBpmFormField(String fieldName, String fieldDesc, String fieldType, short controlType, short isHidden) {
        FormField bpmFormField = new FormField();
        bpmFormField.setFieldName(fieldName);
        bpmFormField.setFieldDesc(fieldDesc);
        bpmFormField.setFieldType(fieldType);
        bpmFormField.setControlType(controlType);
        bpmFormField.setIsHidden(isHidden);
        return bpmFormField;
    }

    /**
     * 预览数据
     *
     * @param alias
     * @param params      表查询的相关信息参数
     * @param queryParams 数据库查询条件参数
     * @return html
     * @throws Exception
     */
    public String getDisplay(String alias, Map<String, Object> params,
                             Map<String, Object> queryParams) throws Exception {
        //当前登陆用户id
        String curUserId = ContextUtil.getCurrentUserId();
        //当前用户的组织id
        String curOrgId = ContextUtil.getCurrentOrgId();

        //设置常用变量到params中，其实就是把当前登陆用户id和组织id放到params中
        CommonVar.setCurrentVars(params);

        // 获取权限map
        Map<String, Object> rightMap = this.getRightMap(curUserId, curOrgId);
        //获取业务模板数据
        DataTemplate bpmDataTemplate = dataTemplateDao.getByFormKey(alias);

        //检查请求是否是查询请求
        if (!params.containsKey(DataTemplate.PARAMS_KEY_ISQUERYDATA)) {
            params.put(DataTemplate.PARAMS_KEY_ISQUERYDATA, bpmDataTemplate.getIsQuery() == 0);
        }

        //获取业务模板对应的自定义表的相关信息
        FormTable bpmFormTable = formTableService.getByTableId(
                bpmDataTemplate.getTableId(), FormField.FIELD_NORMAL_HIDDEN);
        //格式化的数据
        Map<String, Object> formatData = this.getFormatDataMap(bpmFormTable, bpmDataTemplate.getFormKey());

        String baseURL = (String) params.get("__baseURL");
        // 初始化对对当前用户有过滤效果的过滤条件，把filterField的内容重新赋值，只保留对当前用户有过滤效果的内容
        bpmDataTemplate = this.getRightFilterField(bpmDataTemplate, rightMap, baseURL, alias);
        //过滤条件设定的Key
        String filterKey = this.getFilterKey(bpmDataTemplate, params);
        // 构建URL
        String tableIdCode = (String) params.get("__tic");
        if (tableIdCode == null) {
            tableIdCode = "";
            params.put("__tic", "");
        }
        // 点击排序字段后的排序
        Map<String, String> sortMap = getSortMap(params, tableIdCode);

        String templateHtml = bpmDataTemplate.getTemplateHtml();
        // 如果取不到默认模板，就重新解析
        if (StringUtil.isEmpty(templateHtml)) {
            templateHtml = this.generateTemplate(bpmDataTemplate);
        }
        Map<String, Object> map = new HashMap<>(20);
        Map<String, Boolean> managePermission = getManagePermission(DataTemplate.RIGHT_TYPE_MANAGE,
                bpmDataTemplate.getManageField(), rightMap);
        params.put("__defId__", bpmDataTemplate.getDefId());
        params.put("alias", bpmDataTemplate.getFormKey());

        // 第二次解析模板
        map.clear();
        map.put("bpmDataTemplate", bpmDataTemplate);
        map.put("sort", sortMap);
        map.put("sortField", sortMap.get("sortField"));
        map.put("orderSeq", sortMap.get("orderSeq"));
        map.put("tableIdCode", tableIdCode);
        map.put("service", this);
        // 当前字段的权限
        Map<String, Boolean> permissionMap = getPermission(DataTemplate.RIGHT_TYPE_SHOW, bpmDataTemplate.getDisplayField(), rightMap);
        map.put("permission", permissionMap);
        map.put("permissionStr", JSONObject.fromObject(permissionMap).toString());
        // 功能按钮的权限
        map.put("managePermission", managePermission);
        map.put("filterKey", filterKey);
        map.put("actionUrl", getActionUrl(params));
        map.put("checkbox", isCheckbox(managePermission));
        map.put("formatData", formatData);
        map.put("param", queryParams);
        map.put("alias", alias);
        map.put("defId", bpmDataTemplate.getDefId());
        //设置查询条件为动态传入时候需要参数字符串
        map.put("paramStr", params.get("paramStr"));
        String html = freemarkEngine.parseByStringTemplate(map, templateHtml);
        return html;
    }

    /**
     * 预览数据
     *
     * @param alias
     * @param params 表查询的相关信息参数
     * @return html
     * @throws Exception
     */
    public DataTemplate getDataList(String alias, Map<String, Object> params) throws Exception {
        //当前登陆用户id
        String curUserId = ContextUtil.getCurrentUserId();
        //当前用户的组织id
        String curOrgId = ContextUtil.getCurrentOrgId();

        //设置常用变量到params中，其实就是把当前登陆用户id和组织id放到params中
        CommonVar.setCurrentVars(params);

        // 获取权限map
        Map<String, Object> rightMap = this.getRightMap(curUserId, curOrgId);
        //获取业务模板数据
        DataTemplate bpmDataTemplate = dataTemplateDao.getByFormKey(alias);

        //检查请求是否是查询请求
        if (!params.containsKey(DataTemplate.PARAMS_KEY_ISQUERYDATA)) {
            params.put(DataTemplate.PARAMS_KEY_ISQUERYDATA, bpmDataTemplate.getIsQuery() == 0);
        }

        //获取业务模板对应的自定义表的相关信息
        FormTable bpmFormTable = formTableService.getByTableId(
                bpmDataTemplate.getTableId(), FormField.FIELD_NORMAL_HIDDEN);
        //格式化的数据
        Map<String, Object> formatData = this.getFormatDataMap(bpmFormTable, bpmDataTemplate.getFormKey());

        String baseURL = (String) params.get("__baseURL");
        // 初始化对对当前用户有过滤效果的过滤条件，把filterField的内容重新赋值，只保留对当前用户有过滤效果的内容
        bpmDataTemplate = this.getRightFilterField(bpmDataTemplate, rightMap, baseURL, alias);
        //过滤条件设定的Key
        String filterKey = this.getFilterKey(bpmDataTemplate, params);
        // 构建URL
        String tableIdCode = (String) params.get("__tic");
        if (tableIdCode == null) {
            tableIdCode = "";
            params.put("__tic", "");
        }
        // 点击排序字段后的排序
        Map<String, String> sortMap = getSortMap(params, tableIdCode);

        // 取得当前过滤的条件
        JSONObject filterJson = this.getFilterFieldJson(bpmDataTemplate, params);

        boolean isQueryData = (Boolean) params
                .get(DataTemplate.PARAMS_KEY_ISQUERYDATA);
        if (isQueryData) {
            // 取得数据
            bpmDataTemplate = this.getData(bpmDataTemplate, bpmFormTable,
                    rightMap, params, sortMap, formatData, filterJson);
        }
        //解密主表字段数据
        decryptMainField(bpmDataTemplate, bpmFormTable);
        return bpmDataTemplate;
    }

    /**
     * 查询时候解密主表的数据(查询时候只显示主表的列数据)
     *
     * @param bpmDataTemplate
     * @param bpmFormTable
     * @throws Exception
     */
    private void decryptMainField(DataTemplate bpmDataTemplate, FormTable bpmFormTable) throws Exception {
        List<Map<String, Object>> list = bpmDataTemplate.getList();
        String tableId = bpmFormTable.getTableId();
        //解密主表的数据
        List<FormField> encryptList = formFieldService.getEncryptFieldByTableId(tableId);
        decryptEncryptFileds(list, encryptList, FormField.ISENCRYPT_NOT);
    }

    /**
     * 是否有选择框
     *
     * <pre>
     * 	导出、打印
     * </pre>
     *
     * @param managePermission
     * @return
     */
    private boolean isCheckbox(Map<String, Boolean> managePermission) {
        if (BeanUtils.isEmpty(managePermission)) {
            return false;
        }
        if (managePermission.containsKey(DataTemplate.MANAGE_TYPE_EXPORT)) {
            if (managePermission.get(DataTemplate.MANAGE_TYPE_EXPORT)) {
                return true;
            }
        }
        if (managePermission.containsKey(DataTemplate.MANAGE_TYPE_PRINT)) {
            if (managePermission.get(DataTemplate.MANAGE_TYPE_PRINT)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 获取Action的URl
     *
     * @param params
     * @return
     * @throws Exception
     */
    private Map<String, String> getActionUrl(Map<String, Object> params) {
        Map<String, String> map = new HashMap<>(20);
        String baseURL = (String) params.get("__baseURL");
        String defId = null == params.get(DataTemplate.PARAMS_KEY_DEFID) ? "" : params.get(DataTemplate.PARAMS_KEY_DEFID).toString();
        String alias = null == params.get(DataTemplate.PARAMS_KEY_ALIAS) ? "" : params.get(DataTemplate.PARAMS_KEY_ALIAS).toString();
        String toReplace = "/getDisplay_" + alias;
        String addBaseURL = baseURL.replace(toReplace, "/editData_" + alias);
        String editBaseURL = baseURL.replace(toReplace, "/editData_" + alias);
        String deleteBaseURL = baseURL.replace(toReplace, "/deleteData_" + alias);
        String batchDeleteBaseURL = baseURL.replace(toReplace, "/batchDel_" + alias);
        String detailBaseURL = baseURL.replace(toReplace, "/detailData_" + alias);
        String startBaseURL = BeanUtils.isEmpty(defId) ? "" : ("/workflow/runTask/startFlowForm&defId=" + defId);
        // 导出
        String exportBaseURL = baseURL.replace(toReplace, "/exportData_" + alias)
                + '?'
                + DataTemplate.PARAMS_KEY_FILTERKEY
                + "="
                + params.get(DataTemplate.PARAMS_KEY_FILTERKEY);

        // 导入
        String importBaseURL = baseURL.replace(toReplace,
                "/importData_" + alias);

        map.put(DataTemplate.MANAGE_TYPE_ADD, addBaseURL);
        map.put(DataTemplate.MANAGE_TYPE_EDIT, editBaseURL);
        map.put(DataTemplate.MANAGE_TYPE_DEL, deleteBaseURL);
        map.put(DataTemplate.MANAGE_TYPE_BATCH_DEL, batchDeleteBaseURL);
        map.put(DataTemplate.MANAGE_TYPE_DETAIL, detailBaseURL);
        map.put(DataTemplate.MANAGE_TYPE_START, startBaseURL);
        map.put(DataTemplate.MANAGE_TYPE_RESTART, startBaseURL);
        map.put(DataTemplate.MANAGE_TYPE_EXPORT, exportBaseURL);
        map.put(DataTemplate.MANAGE_TYPE_IMPORT, importBaseURL);
        return map;
    }

    /**
     * 获取字段的权限
     *
     * @param type
     * @param displayField
     * @param rightMap
     * @return
     */
    private Map<String, Boolean> getPermission(int type, String displayField,
                                               Map<String, Object> rightMap) {
        JSONArray jsonAry = JSONArray.fromObject(displayField);
        return QueryUtil.getPermissionMap(type, jsonAry, rightMap);
    }


    /**
     * 获取管理的权限
     *
     * @param type
     * @param manageField
     * @param rightMap
     * @return
     */
    public Map<String, Boolean> getManagePermission(int type,
                                                    String manageField, Map<String, Object> rightMap) {
        if (StringUtils.isEmpty(manageField)) {
            return null;
        }
        JSONArray jsonAry = JSONArray.fromObject(manageField);
        return QueryUtil.getPermissionMap(type, jsonAry, rightMap);
    }

    /**
     * 排除的参数
     *
     * @param params
     * @param queryParams
     * @return
     */
    private List<String> getExcludes(Map<String, Object> params,
                                     Map<String, Object> queryParams) {

        List<String> excludes = new ArrayList<String>();
        for (String key : params.keySet()) {
            if (key.endsWith("__ns__")) {
                excludes.add(key);
            }
            if (key.endsWith("__pk__")) {
                excludes.add(key);
            }
        }
        excludes.add("rand");
        excludes.add("__baseURL");
        excludes.add("__tic");
        excludes.add("__displayId");
        // 排除查询的
        for (Map.Entry<String, Object> entry : queryParams.entrySet()) {
            String key = entry.getKey();
            if (!key.startsWith("Q_")) {
                continue;
            }
            String[] aryParaKey = key.split("_");
            if (aryParaKey.length < 3) {
                continue;
            }
            String paraName = key.substring(2, key.lastIndexOf("_"));
            excludes.add(paraName);
        }

        return excludes;
    }

    /**
     * 获得查询的Map
     *
     * @param url
     * @return
     */
    private static Map<String, Object> getQueryStringMap(String url) {
        Map<String, Object> map = new HashMap<>(16);
        int idx1 = url.indexOf("?");
        if (idx1 > 0) {
            String queryStr = url.substring(idx1 + 1);
            String[] queryNodeAry = queryStr.split("&");
            for (String queryNode : queryNodeAry) {
                String[] strAry = queryNode.split("=");
                if (strAry.length == 1) {
                    map.put(strAry[0], null);
                } else {
                    map.put(strAry[0].trim(), strAry[1]);
                }
            }
        }
        return map;
    }

    /**
     * 获取当前用户的权限Map
     * Map其实就是包括：用户的角色、岗位、组织、可管理组织列表
     *
     * @param userId
     * @param curOrgId
     * @return
     */
    public Map<String, Object> getRightMap(String userId, String curOrgId) {
        Map<String, Object> map = new HashMap<>(16);
        List<SysRole> roles = sysRoleService.queryByUserId(userId);
        List<Position> positions = positionService.queryByUserId(userId);
        SysOrg org = sysOrgService.getOrgByUserId(userId);
        map.put("userId", userId);
        map.put("curOrgId", curOrgId);
        map.put("roles", roles);
        map.put("positions", positions);
        map.put("orgs", org);
        return map;
    }


    /**
     * 过滤条件KEY
     *
     * @param bpmDataTemplate
     * @param params
     * @return
     */
    private String getFilterKey(DataTemplate bpmDataTemplate,
                                Map<String, Object> params) {
        Object key = params.get(DataTemplate.PARAMS_KEY_FILTERKEY);
        if (BeanUtils.isNotEmpty(key)) {
            return (String) key;
        }
        String filterField = bpmDataTemplate.getFilterField();
        if (StringUtils.isEmpty(filterField)) {
            return "";
        }
        JSONArray jsonAry = JSONArray.fromObject(filterField);
        if (JSONUtils.isNull(jsonAry) || jsonAry.size() == 0) {
            return "";
        }
        //拿第一个过滤字段key
        JSONObject jsonObj = jsonAry.getJSONObject(0);
        String filterKey = jsonObj.getString("key");
        params.put(DataTemplate.PARAMS_KEY_FILTERKEY, filterKey);
        return filterKey;
    }

    /**
     * 获取有权限的过滤字段
     *
     * @param bpmDataTemplate
     * @param rightMap
     * @param baseURL
     * @return
     */
    private DataTemplate getRightFilterField(DataTemplate bpmDataTemplate, Map<String, Object> rightMap,
                                             String baseURL, String alias) {
        //获取过滤条件
        String filterField = bpmDataTemplate.getFilterField();
        JSONArray jsonArray = JSONArray.fromObject(filterField);
        String destFilterField = new JSONArray().toString();
        if (JSONObjectUtil.isEmpty(jsonArray)) {
            bpmDataTemplate.setFilterField(destFilterField);
            return bpmDataTemplate;
        }
        String toReplaceUrl = "dataList_" + alias;
        String sourceUrl = "getDisplay_" + alias;
        String url = baseURL.replace(sourceUrl, toReplaceUrl);
        // 有权限过滤条件
        JSONArray jsonAry = new JSONArray();
        for (Object obj : jsonArray) {
            JSONObject jObj = (JSONObject) obj;
            JSONArray rightAry = JSONArray.fromObject(jObj.get("right"));
            for (Object jo : rightAry) {
                JSONObject permission = (JSONObject) jo;
                if (QueryUtil.hasRight(permission, rightMap)) {
                    jsonAry.add(obj);
                    break;
                }
            }
        }
        if (JSONObjectUtil.isEmpty(jsonAry)) {
            bpmDataTemplate.setFilterField(destFilterField);
            return bpmDataTemplate;
        }
        JSONArray destJsonAry = new JSONArray();
        for (Object obj : jsonAry) {
            JSONObject json = (JSONObject) obj;
            String name = json.getString("name");
            String key = json.getString("key");
            // 展示字段
            json.accumulate("desc", StringUtil.subString(name, 5, "..."));
            json.accumulate("url", url + "?"
                    + DataTemplate.PARAMS_KEY_FILTERKEY + "=" + key);
            destJsonAry.add(json);
        }
        bpmDataTemplate.setFilterField(destJsonAry.toString());
        return bpmDataTemplate;
    }

    /**
     * 获取排序的字段和标识
     *
     * @param params
     * @param tableIdCode
     * @return
     */
    private Map<String, String> getSortMap(Map<String, Object> params, String tableIdCode) {
        Map<String, String> sortMap = new HashMap<>(16);
        // 排序
        String sortField = null;
        String orderSeq = DataTemplate.DESC;
        String newSortField = null;
        if (params.get(tableIdCode + DataTemplate.SORTFIELD) != null) {
            sortField = (String) params.get(tableIdCode + DataTemplate.SORTFIELD);
        }
        if (params.get(tableIdCode + DataTemplate.ORDERSEQ) != null) {
            orderSeq = (String) params.get(tableIdCode + DataTemplate.ORDERSEQ);
        }
        if (params.get(tableIdCode + DataTemplate.NS) != null) {
            newSortField = (String) params.get(tableIdCode + DataTemplate.NS);
        }
        if (StringUtil.isNotEmpty(newSortField)) {
            if (newSortField.equals(sortField)) {
                if (orderSeq.equals(DataTemplate.ASC)) {
                    orderSeq = DataTemplate.DESC;
                } else {
                    orderSeq = DataTemplate.ASC;
                }
            }
            sortField = newSortField;
            params.put(tableIdCode + DataTemplate.SORTFIELD, sortField);
            params.put(tableIdCode + DataTemplate.ORDERSEQ, orderSeq);
            sortMap.put("sortField", sortField);
            sortMap.put("orderSeq", orderSeq);
        }
        return sortMap;
    }


    /**
     * 获取过滤条件
     *
     * @param bpmDataTemplate 业务数据模板
     * @param params          参数
     * @return
     */
    private JSONObject getFilterFieldJson(DataTemplate bpmDataTemplate, Map<String, Object> params) {

        JSONObject filterJson = this.getFilterJson(bpmDataTemplate.getFilterField(), params);
        if (JSONObjectUtil.isEmpty(filterJson) && bpmDataTemplate.getIsFilter().shortValue() == 0) {
            JSONArray jsonAry = new JSONArray();
            jsonAry.add(getDefaultFilterJson());
            bpmDataTemplate.setFilterField(jsonAry.toString());
        }
        return filterJson;
    }

    /**
     * 默认的条件
     *
     * @return
     */
    private JSONObject getDefaultFilterJson() {
        return JSONObject.fromObject("{\"name\":\"默认条件 \",\"key\":\"Default\",\"type\":\"1\",\"condition\":\"[]\",\"right\":[{\"s\":3,\"type\":\"everyone\",\"id\":\"\",\"name\":\"\",\"script\":\"\"}]}");
    }

    /**
     * 取出满足过滤条件的JSON对象
     *
     * @param filterField
     * @param params      参数
     * @return
     */
    private JSONObject getFilterJson(String filterField, Map<String, Object> params) {
        JSONObject jsonObj = null;
        if (StringUtils.isEmpty(filterField)) {
            return jsonObj;
        }
        JSONArray jsonAry = JSONArray.fromObject(filterField);
        if (JSONObjectUtil.isEmpty(jsonAry)) {
            return jsonObj;
        }
        // 取得满足key的条件
        String filterKey = (String) params.get(DataTemplate.PARAMS_KEY_FILTERKEY);
        if (StringUtils.isEmpty(filterKey)) {
            jsonObj = jsonAry.getJSONObject(0);
        } else {
            for (Object obj : jsonAry) {
                JSONObject jObj = (JSONObject) obj;
                String key = (String) jObj.get("key");
                if (key.equals(filterKey)) {
                    // 取出满足的Key
                    jsonObj = jObj;
                    break;
                }
            }
        }
        return jsonObj;
    }


    /**
     * 取的数据
     *
     * @param bpmDataTemplate
     * @param bpmFormTable
     * @param rightMap        权限数据
     * @param params          页面传过来的参数
     * @param sortMap         排序map
     * @param formatData      格式化的数据
     * @param filterJson
     * @return
     * @throws Exception
     */
    private DataTemplate getData(DataTemplate bpmDataTemplate,
                                 FormTable bpmFormTable, Map<String, Object> rightMap,
                                 Map<String, Object> params, Map<String, String> sortMap,
                                 Map<String, Object> formatData, JSONObject filterJson) {
        String filterField = bpmDataTemplate.getFilterField();
        JSONArray jsonArray = JSONArray.fromObject(filterField);
        setBpmDataTemplateData(bpmDataTemplate, bpmFormTable, rightMap, params, sortMap, formatData, filterJson);
        if (!JSONObjectUtil.isEmpty(jsonArray)) {
            // 有权限过滤条件
            for (Object obj : jsonArray) {
                JSONObject jObj = (JSONObject) obj;
                JSONArray rightAry = JSONArray.fromObject(jObj.get("right"));
                for (Object jo : rightAry) {
                    JSONObject permission = (JSONObject) jo;
                    if (QueryUtil.hasRight(permission, rightMap)) {
                        if (permission.containsKey("srid")) {
                            JSONObject source = permission.getJSONObject("source");
                            String v = source.getString("v");
                            String ids = source.getString("ids");
                            if ("user".equals(v)) {
                                for (String id : ids.split(",")) {
                                    params.put("[CUR_USER]", id);
                                    params.put("[CUR_ORG]", sysOrgService.getOrgByUserId(id));
                                    setBpmDataTemplateData(bpmDataTemplate, bpmFormTable, rightMap, params, sortMap, formatData, filterJson);
                                }
                            } else if ("org".equals(v)) {
                                for (String id : ids.split(",")) {
                                    params.put("[CUR_ORG]", id);
                                    setBpmDataTemplateData(bpmDataTemplate, bpmFormTable, rightMap, params, sortMap, formatData, filterJson);
                                }
                            }
                        }
                    }
                }
            }
        }
        return bpmDataTemplate;
    }

    private void setBpmDataTemplateData(DataTemplate bpmDataTemplate,
                                        FormTable bpmFormTable, Map<String, Object> rightMap,
                                        Map<String, Object> params, Map<String, String> sortMap,
                                        Map<String, Object> formatData, JSONObject filterJson) {
        List<Map<String, Object>> list;
        String sql;
        // 是否需要分页。
        if (bpmDataTemplate.getNeedPage().shortValue() == 1) {
            int currentPage = 1;
            if (params.containsKey("page")) {
                currentPage = Integer.parseInt(params.get("page").toString());
            }
            int pageSize = bpmDataTemplate.getPageSize();
            if (params.containsKey("limit")) {
                pageSize = Integer.parseInt(params.get("limit").toString());
            }
            // 获取SQL
            sql = this.getSQL(filterJson, bpmDataTemplate, bpmFormTable,
                    rightMap, params, sortMap);
            sql = sql.toLowerCase();
            Map<String, Object> params2 = new CaseInsensitiveMap();
            params2.putAll(params);

            list = jdbcTemplateUtil.getPage(bpmFormTable.getDsAlias(), currentPage, pageSize, sql, params2);
            bpmDataTemplate.setPageBean(((PageList) list).getPageBean());
        } else {
            // 获取数据
            sql = this.getSQL(filterJson, bpmDataTemplate, bpmFormTable,
                    rightMap, params, sortMap);
            list = JdbcTemplateUtil.getNamedParameterJdbcTemplate(bpmFormTable.getDsAlias()).queryForList(sql, params);
        }
        // 整理的是List
        if (BeanUtils.isNotEmpty(list)) {
            list = getDataList(list, bpmFormTable, formatData, true);
        }
        List<Map<String, Object>> oldList = bpmDataTemplate.getList();
        if (BeanUtils.isEmpty(oldList)) {
            bpmDataTemplate.setList(list);
        } else {
            List addList = new ArrayList();
            for (Map<String, Object> obj : list) {
                String id = obj.get("ID").toString();
                boolean isExist = false;
                for (Map<String, Object> oldObj : oldList) {
                    String oldId = oldObj.get("ID").toString();
                    if (oldId.equalsIgnoreCase(id)) {
                        isExist = true;
                    }
                }
                if (!isExist) {
                    addList.add(obj);
                }
            }
            oldList.addAll(addList);
        }
    }

    /**
     * 获取整理过的数据
     *
     * @param list
     * @param bpmFormTable
     * @param formatData
     * @param isMain
     * @return
     */
    private List<Map<String, Object>> getDataList(
            List<Map<String, Object>> list, FormTable bpmFormTable,
            Map<String, Object> formatData, boolean isMain) {

        String pkField = bpmFormTable.getPkField();
        String relation = StringUtils.isEmpty(bpmFormTable.getRelation()) ? TableModel.FK_COLUMN_NAME
                : bpmFormTable.getRelation();
        List<Map<String, Object>> dataList = new ArrayList<>();
        Map<String, String> fieldMap = convertFieldList(bpmFormTable);

        for (Map<String, Object> map : list) {
            Map<String, Object> mapData = new HashMap<>(16);
            Set<String> keySet = map.keySet();
            for (Iterator<String> it = keySet.iterator(); it.hasNext(); ) {
                String key = it.next().toUpperCase();
                if (fieldMap.containsKey(key)) {
                    String name = fieldMap.get(key);
                    Object o = getFormatData(map.get(key), name, formatData);
                    mapData.put(name, o);
                } else {
                    mapData.put(key, map.get(key));
                }
            }

            mapData.put(pkField, map.get(pkField));
            // 加入外键
            if (!isMain) {
                mapData.put(relation, map.get(relation));
            }
            dataList.add(mapData);
        }
        return dataList;
    }

    /**
     * 格式日期
     *
     * @param val
     * @param datefmt
     * @return
     */
    private String sqlToDate(String val, String datefmt) {
        StringBuffer sb = new StringBuffer();
        sb.append("TO_DATE('")
                .append(val)
                .append("','")
                .append(StringUtils.isEmpty(datefmt) ? StringPool.DATE_FORMAT_DATE
                        : datefmt).append("')");
        return sb.toString();
    }

    /**
     * 获取数据格式化的值
     *
     * @param o
     * @param name
     * @param formatData
     * @return
     */
    private Object getFormatData(Object o, String name,
                                 Map<String, Object> formatData) {
        if (BeanUtils.isEmpty(o)) {
            return "";
        }
        // 如果是日期的
        if (o instanceof Date) {
            String style = StringPool.DATE_FORMAT_DATE;
            if (formatData.containsKey(name)) {
                Object format = formatData.get(name);
                if (BeanUtils.isNotEmpty(format)) {
                    style = (String) format;
                }
            }
            o = DateFormatUtil.format((Date) o, style);
        }
        // 如果是 下拉框，单选框的
        /*else if (formatData.containsKey(name)) {
            Object obj = formatData.get(name);
            if (BeanUtils.isNotEmpty(obj)) {
                if (obj instanceof Map) {
                    Map<?, ?> map = (Map<?, ?>) obj;
                    String[] selectStrs = o.toString().split(",");
                    //复选框多选
                    if (selectStrs.length > 1) {
                        String result = "";
                        for (String str : selectStrs) {
                            result += map.get(str) + ",";
                        }
                        o = result.substring(0, result.length() - 1);
                    } else {
                        o = map.get(o + "");
                    }
                    if (BeanUtils.isEmpty(o)) {
                        o = "";
                    }
                }
            }
        }*/
        return o.toString();
    }

    private Map<String, String> convertFieldList(FormTable bpmFormTable) {
        Map<String, String> map = new HashMap<>(16);
        List<FormField> fieldList = bpmFormTable.getFieldList();
        String source = bpmFormTable.getIsExternal() == FormTable.NOT_EXTERNAL ? DataTemplate.SOURCE_CUSTOM_TABLE
                : DataTemplate.SOURCE_OTHER_TABLE;
        for (FormField bpmFormField : fieldList) {
            String name = bpmFormField.getFieldName();
            String nameUp = bpmFormField.getFieldName().toUpperCase();
            String fixFieldName = this.fixFieldName(nameUp, source);
            map.put(fixFieldName, name);
        }
        return map;
    }

    /**
     * 修正字段名
     *
     * @param fieldName 字段名
     * @param source    数据来源 1.表示自定义表（需要加F_修正）
     * @return
     */
    private String fixFieldName(String fieldName, String source) {
        return fixFieldName(fieldName, source, "");
    }

    /**
     * 修正字段名
     *
     * @param fieldName 字段名
     * @param source    数据来源 1.表示自定义表
     * @param prefix    前缀修正
     * @return
     */
    private String fixFieldName(String fieldName, String source, String prefix) {
        if (StringUtils.isEmpty(fieldName) || StringUtils.isEmpty(source)) {
            return fieldName;
        }
        if (StringUtils.isNotEmpty(prefix)) {
            fieldName = prefix.toLowerCase() + "." + fieldName;
        }
        return fieldName;
    }


    /**
     * 获取拼接的SQL
     *
     * @param filterJson
     * @param bpmDataTemplate
     * @param bpmFormTable
     * @param rightMap
     * @param params
     * @param sortMap
     * @return
     */
    private String getSQL(JSONObject filterJson,
                          DataTemplate bpmDataTemplate, FormTable bpmFormTable,
                          Map<String, Object> rightMap, Map<String, Object> params,
                          Map<String, String> sortMap) {
        if (JSONObjectUtil.isNotEmpty(filterJson)) {
            String type = (String) filterJson.get("type");
            if ("2".equals(type)) {
                String condition = (String) filterJson.get("condition");
                Map<String, Object> paramsMap = new HashMap<>(16);
                paramsMap.put("map", params);
                return groovyScriptEngine.executeString(condition, paramsMap);
            }
        }

        // 以下是拼接SQL的
        String source = bpmDataTemplate.getSource();
        String tableName = bpmFormTable.getTableName();
        String table = tableName.toLowerCase();
        Map<String, String> tableMap = new HashMap<>(20);
        tableMap.put(table, fixTableName(tableName, source));
        //2020-03-18 jyq添加，添加与流程的关联表
        tableMap.put(DataTemplate.BUS_TABLE.toLowerCase(), DataTemplate.BUS_TABLE);
        StringBuffer sql = new StringBuffer("SELECT ");
        // 主从表关联关系
        Map<String, Map<String, String>> relationMap = new HashMap<>(20);
        //2020-03-18 jyq添加，添加与流程的关联表的关联关系
        Map<String, String> relation = new HashMap<>(1);
        relation.put(table,DataTemplate.BUS_TABLE_PK_STR);
        relationMap.put(DataTemplate.BUS_TABLE.toLowerCase(),relation);

        Map<String, FormField> bpmFormFieldMap = this.getBpmFormFieldMap(bpmFormTable);

        String pkField = bpmFormTable.getPkField();
        // where sql
        String where = "";
        // 过滤条件 sql
        where = this.getFilterSQL(filterJson, tableMap, relationMap);

        // 查询条件 sql
       where = this.getQuerySQL(bpmDataTemplate, where, tableName, params,
                bpmFormFieldMap);

        // 表名的SQL
        sql.append(this.getFromTableSQL(pkField, bpmDataTemplate, tableName,
                tableMap, rightMap,bpmFormTable.getIsExternal()));

        if (StringUtils.isNotEmpty(where)) {
            // 多表之间的关联
            sql.append(" WHERE ")
                    .append(getTableWhere(table, pkField, where, tableMap,
                            relationMap));
        } else {
            Set<Map.Entry<String, String>> set = tableMap.entrySet();
            if (set.size() > 1) {
                sql.append(" WHERE ").append(
                        getTableWhere(table, pkField, "1=1", tableMap,
                                relationMap));
            }
        }

        // order sql 排序
        return this.getOrderBySql(sql, pkField, bpmDataTemplate.getSortField(),
                sortMap, source);

    }

    @Autowired
    private BpmBusLink bpmBusLink;

    /**
     * 获取表之间的关系
     *
     * @param table
     * @param pk
     * @param where
     * @param tableMap
     * @param relationMap
     * @return
     */
    private String getTableWhere(String table, String pk, String where,
                                 Map<String, String> tableMap,
                                 Map<String, Map<String, String>> relationMap) {
        Set<Map.Entry<String, String>> set = tableMap.entrySet();
        // 只有一个表
        if (set.size() == 1) {
            return where;
        } else {
            StringBuffer sb = new StringBuffer();
            // 从表与主表的关系
            for (Iterator<Map.Entry<String, Map<String, String>>> it = relationMap
                    .entrySet().iterator(); it.hasNext(); ) {
                Map.Entry<String, Map<String, String>> e = it.next();
                String key = e.getKey();
                String fk = e.getValue().get(table);
                if (StringUtils.isNotEmpty(fk)) {
                    String mainKey = table + "." + pk;
                    String subKey = key + "." + fk;
                    sb.append(" AND ").append(mainKey).append("=")
                            .append(subKey);
                }
                //如果是涉及业务中间表的查询。添加查询条件
                if (DataTemplate.BUS_TABLE.equalsIgnoreCase(key) && bpmBusLink.isSupportPartition()) {
                    sb.append(" AND " + key + "." + BpmBusLink.BUS_FORM_TABLE + " = '" + table.toLowerCase() + "'");
                }
            }
            where = where + sb.toString();
        }
        return where;
    }

    /**
     * 获得order by的SQL
     *
     * @param sql
     * @param pkField
     * @param sortField
     * @param sortMap
     * @param source
     * @return
     */
    private String getOrderBySql(StringBuffer sql, String pkField,
                                 String sortField, Map<String, String> sortMap, String source) {
        StringBuffer orderBy = new StringBuffer();
        if (BeanUtils.isNotEmpty(sortMap)) {
            orderBy.append(" ORDER BY ")
                    .append(fixFieldName(sortMap.get("sortField"), source, ""))
                    .append(" ").append(sortMap.get("orderSeq"));
        } else {
            // 取设置的排序
            if (StringUtils.isNotEmpty(sortField)) {
                String sortSql = this.getSortSQL(sortField, source);
                if (StringUtils.isNotEmpty(sortSql)) {
                    orderBy.append(" ORDER BY ").append(sortSql);
                }
            }
        }
        // 如果没有排序 则用主键排序，避免分页问题
        if (StringUtils.isEmpty(orderBy.toString())) {
            orderBy.append(" ORDER BY ").append(pkField).append(" ASC");
        }

        StringBuffer select = new StringBuffer();
        select.append("select *  from  (").append(sql).append(") t ")
                .append(orderBy);
        return select.toString();
    }

    /**
     * 获取排序的SQL
     *
     * @param sortField
     * @param source
     * @return
     */
    private String getSortSQL(String sortField, String source) {
        StringBuffer sb = new StringBuffer();
        JSONArray jsonArray = JSONArray.fromObject(sortField);
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObj = (JSONObject) jsonArray.get(i);
            String name = (String) jsonObj.get("name");
            String sort = (String) jsonObj.get("sort");
            if (StringUtils.isEmpty(sort)) {
                sort = "desc";
            }
            sb.append(this.fixFieldName(name, source, "")).append(" ").append(sort).append(",");
        }
        if (sb.length() > 0) {
            return sb.substring(0, sb.length() - 1);
        }
        return sb.toString();
    }

    /**
     * 获取SQL的表
     *
     * @param pkField
     * @param bpmDataTemplate
     * @param tableName
     * @param tableMap
     * @param rightMap
     * @param isExternal 是否外部表
     * @return
     */
    private String getFromTableSQL(String pkField,
                                   DataTemplate bpmDataTemplate, String tableName,
                                   Map<String, String> tableMap, Map<String, Object> rightMap,int isExternal) {
        // 拼接Select的SQL
        StringBuffer sb = new StringBuffer(tableName + ".TENANTID,");
        if(isExternal == FormTable.NOT_EXTERNAL){
            sb.append(tableName).append(".createtime,");
        }
        String c = ",";
        String displayField = bpmDataTemplate.getDisplayField();
        if (StringUtils.isNotEmpty(displayField)) {
            sb.append(fixFieldName(pkField, DataTemplate.SOURCE_OTHER_TABLE,
                    tableName)).append(c);
            Map<String, Boolean> map = this.getPermission(
                    DataTemplate.RIGHT_TYPE_SHOW, displayField, rightMap);
            for (Iterator<Map.Entry<String, Boolean>> it = map.entrySet().iterator(); it.hasNext(); ) {
                Map.Entry<String, Boolean> e = it.next();
                String key = e.getKey();
                if (key.equalsIgnoreCase(pkField)) {
                    continue;
                }
                sb.append(this.fixFieldName(key, bpmDataTemplate.getSource(), tableName)).append(c);
            }

            String busLinkTable = DataTemplate.BUS_TABLE.toLowerCase();
            if (tableMap.containsKey(busLinkTable)) {
                sb.append(busLinkTable + ".*");
            } else {
                sb.deleteCharAt(sb.length() - 1);
            }
        } else {
            sb.append(tableName).append(".*");
        }

        // 拼接from后的SQL
        StringBuffer from = new StringBuffer();
        for (Iterator<Map.Entry<String, String>> it = tableMap.entrySet()
                .iterator(); it.hasNext(); ) {
            Map.Entry<String, String> e = it.next();
            String key = e.getKey();
            String val = e.getValue();
            from.append(val).append(" ").append(key).append(c);
        }
        String s1 = from.substring(0, from.length() - 1);
        return sb.toString() + " from " + s1;
    }

    /**
     * 根据查询条件列表，计算Where SQL 语句
     *
     * @param bpmDataTemplate
     * @param where
     * @param tableName
     * @param params
     * @param bpmFormFieldMap
     * @return
     */
    private String getQuerySQL(DataTemplate bpmDataTemplate, String where,
                               String tableName, Map<String, Object> params,
                               Map<String, FormField> bpmFormFieldMap) {
        String tenantId = ContextUtil.getCurrentUserTenantId();
        StringBuffer sb = new StringBuffer(" AND " + tableName + ".TENANTID= '" + tenantId + "'");
        String and = StringUtils.isEmpty(where) ? "" : " AND ";
        //20200513 主表增加密级字段dataPrivacyLevel
        if(params.containsKey(FormConstants.DATA_PRIVACY_LEVEL)){
            List<Integer> secList =(List<Integer>)params.get(FormConstants.DATA_PRIVACY_LEVEL);
            String secStr = StringUtils.join(secList.toArray(),",");
            where += and + " (1=1 AND " + tableName +  "."+ FormConstants.DATA_PRIVACY_LEVEL + " in ("+ secStr +") )";
            and = "AND";
        }
        List<SQLClause> conditionFields = this.getConditionList(bpmDataTemplate
                .getConditionField());
        if (BeanUtils.isEmpty(conditionFields)) {
            return where + and + " (1=1 " + sb.toString() + ") ";
        }
        for (SQLClause condition : conditionFields) {
            this.getCluaseSQL(bpmDataTemplate, tableName, condition, params,
                    bpmFormFieldMap, sb);
        }
        if (sb.length() > 0) {
            where += and + " (1=1 " + sb.toString() + ") ";
        }
        return where;
    }

    /**
     * 计算出WHERE SQL
     *
     * @param bpmDataTemplate
     * @param tableName
     * @param condition
     * @param params
     * @param bpmFormFieldMap
     * @param sb
     */
    private void getCluaseSQL(DataTemplate bpmDataTemplate,
                              String tableName, SQLClause condition, Map<String, Object> params,
                              Map<String, FormField> bpmFormFieldMap, StringBuffer sb) {
        String field = condition.getName();
        String f_field = this.fixFieldName(field, bpmDataTemplate.getSource(),
                tableName);
        String operate = condition.getOperate();
        int valueFrom = condition.getValueFrom();
        String joinType = condition.getJoinType();
        String type = condition.getType();
        joinType = " " + joinType + " ";
        String controlType = condition.getControlType();

        Boolean isSelector = this.isSelector(controlType);
        Map<String, Object> dateMap = getQueryValue(params, field, type, operate);

        Object value = this.getQueryValue(condition, params, field, isSelector);
        if (BeanUtils.isEmpty(value) && BeanUtils.isEmpty(dateMap)) {
            return;
        }
        // 字符串
        if (type.equals(ColumnModel.COLUMNTYPE_VARCHAR)) {
            if (isSelector) {
                f_field = f_field + TableModel.PK_COLUMN_NAME;
                String f_fieldID = field + TableModel.PK_COLUMN_NAME;
                FormField bpmFormField = bpmFormFieldMap.get(f_fieldID);
                if (BeanUtils.isNotEmpty(bpmFormField)) {
                    if (isrMultiSelector(bpmFormField.getControlType())) {
                        // 这个有bug 暂时这样处理吧
                        sb.append(joinType + f_field + " like '%" + value
                                + "%'");
                    } else {
                        // 1","=" "2","!=" "3","like""4","左like" "5","右like"
                        if ("2".equalsIgnoreCase(operate)) {
                            sb.append(joinType + f_field + "!=:" + f_field);
                            params.put(f_field, String.valueOf(value));
                        } else {
                            sb.append(joinType + f_field + "=:" + f_field);
                            params.put(f_field, String.valueOf(value));
                        }
                    }
                }
            } else {
                value = value.toString();
                // 1","=" "2","!=" "3","like""4","左like" "5","右like"
                if ("1".equalsIgnoreCase(operate)) {
                    sb.append(joinType).append(f_field).append("=").append(":")
                            .append(f_field);
                } else if ("2".equalsIgnoreCase(operate)) {
                    sb.append(joinType).append(f_field).append(" != ")
                            .append(":").append(f_field);
                } else if ("3".equalsIgnoreCase(operate)) {
                    value = "%" + value.toString() + "%";
                    sb.append(joinType).append(f_field).append(" LIKE :")
                            .append(f_field);
                } else if ("4".equalsIgnoreCase(operate)) {
                    value = "%" + value.toString();
                    sb.append(joinType).append(f_field).append(" LIKE :")
                            .append(f_field);
                } else if ("5".equalsIgnoreCase(operate)) {
                    value = value.toString() + "%";
                    sb.append(joinType).append(f_field).append(" LIKE :")
                            .append(f_field);
                } else {
                    value = "%" + value.toString() + "%";
                    sb.append(joinType).append(f_field).append(" LIKE :")
                            .append(f_field);
                }
                params.put(f_field, value);
            }
            // 日期
        } else if (type.equals(ColumnModel.COLUMNTYPE_DATE)) {
            // 日期范围特殊处理
            if ("6".equalsIgnoreCase(operate)) {
                if (BeanUtils.isNotEmpty(dateMap
                        .get(DataTemplate.DATE_BEGIN))) {
                    String begingField = DataTemplate.DATE_BEGIN + field;
                    sb.append(joinType + f_field + ">=:" + begingField + " ");
                    params.put(begingField,
                            dateMap.get(DataTemplate.DATE_BEGIN));
                }

                if (BeanUtils.isNotEmpty(dateMap.get(DataTemplate.DATE_END))) {
                    String endField = DataTemplate.DATE_END + field;
                    sb.append(joinType + f_field + "<=:" + endField + " ");
                    params.put(endField, dateMap.get(DataTemplate.DATE_END));
                }
            } else {
                String op = this.getOperate(operate);
                operateData(op, valueFrom, params, sb, value, joinType, f_field, field);
            }
        } else {
            // 否则则是数字
            String op = this.getOperate(operate);
            operateData(op, valueFrom, params, sb, value, joinType, f_field, field);
        }
    }

    private void operateData(String op, int valueFrom, Map<String, Object> params, StringBuffer sb,
                             Object value, String joinType, String f_field, String field) {
        if (valueFrom == FormTable.IS_MAIN) {
            if (params.containsKey(field)) {
                sb.append(joinType + f_field + op + ":" + field + " ");
            }
        } else {
            sb.append(joinType + f_field + op + ":" + field + " ");
            params.put(field, value);
        }

    }

    private Map<String, Object> getQueryValue(Map<String, Object> params, String field, String type, String operate) {
        Map<String, Object> map = new HashMap<>(20);
        if (type.equals(ColumnModel.COLUMNTYPE_DATE) && "6".equalsIgnoreCase(operate)) {
            String beginKey = DataTemplate.DATE_BEGIN + field;
            Object beginVal = null;
            String endKey = DataTemplate.DATE_END + field;
            Object endVal = null;
            if (params.containsKey(beginKey)) {
                beginVal = params.get(beginKey);
            }
            if (params.containsKey(endKey)) {
                endVal = params.get(endKey);
            }
            if (BeanUtils.isNotEmpty(beginVal) || BeanUtils.isNotEmpty(endVal)) {
                map.put(DataTemplate.DATE_BEGIN, beginVal);
                map.put(DataTemplate.DATE_END, endVal);
            }
        }
        return map;
    }

    /**
     * 是否是选择器
     *
     * @param controlType
     * @return
     */
    private Boolean isSelector(String controlType) {
        if (BeanUtils.isEmpty(controlType)) {
            return false;
        }
        if (controlType.equals(String.valueOf(FieldPool.SELECTOR_USER_SINGLE))
                || controlType.equals(String
                .valueOf(FieldPool.SELECTOR_USER_MULTI))
                || controlType.equals(String
                .valueOf(FieldPool.SELECTOR_ORG_SINGLE))
                || controlType.equals(String
                .valueOf(FieldPool.SELECTOR_ORG_MULTI))
                || controlType.equals(String
                .valueOf(FieldPool.SELECTOR_POSITION_SINGLE))
                || controlType.equals(String
                .valueOf(FieldPool.SELECTOR_POSITION_MULTI))
                || controlType.equals(String
                .valueOf(FieldPool.SELECTOR_ROLE_SINGLE))
                || controlType.equals(String
                .valueOf(FieldPool.SELECTOR_ROLE_MULTI))) {
            return true;
        }
        return false;
    }

    /**
     * 获得查询的值
     *
     * @param condition
     * @param params
     * @param field
     * @param isSelector
     * @return
     */
    private Object getQueryValue(SQLClause condition,
                                 Map<String, Object> params, String field, Boolean isSelector) {
        int valueFrom = condition.getValueFrom();
        Object value = null;
        switch (valueFrom) {
            case 1:// 输入
                // 是日期类型，又是日期范围
                if (isSelector) {
                    field = field + TableModel.PK_COLUMN_NAME;
                }
                if (params.containsKey(field)) {
                    value = params.get(field);
                }
                break;
            // 固定值
            case 2:
                value = condition.getValue();
                break;
            // 脚本
            case 3:
                String script = (String) condition.getValue();
                if (StringUtil.isNotEmpty(script)) {
                    value = groovyScriptEngine.executeObject(script, null);
                }
                break;
            // 自定义变量
            case 4:
                // value =
                // sysTableManage.getParameterMap().get(condition.getValue().toString());
                break;
            // 动态输入
            case 5:
                // 是日期类型，又是日期范围
                if (isSelector) {
                    field = field + TableModel.PK_COLUMN_NAME;
                }
                if (params.containsKey(field)) {
                    value = params.get(field);
                }
                break;
            default:
                value = "";
                break;
        }
        return value;
    }

    /**
     * 是否是多选选择器
     *
     * @param controlType
     * @return
     */
    private Boolean isrMultiSelector(Short controlType) {
        if (BeanUtils.isEmpty(controlType)) {
            return false;
        }
        if (controlType == FieldPool.SELECTOR_USER_MULTI
                || controlType == FieldPool.SELECTOR_ORG_MULTI
                || controlType == FieldPool.SELECTOR_POSITION_MULTI
                || controlType == FieldPool.SELECTOR_ROLE_MULTI) {
            return true;
        }
        return false;
    }

    /**
     * 获得操作类型
     *
     * @param operate
     * @return
     */
    private String getOperate(String operate) {
        String op = "=";
        if ("1".equalsIgnoreCase(operate)) {
            op = "=";
        } else if ("2".equalsIgnoreCase(operate)) {
            op = ">";
        } else if ("3".equalsIgnoreCase(operate)) {
            op = "<";
        } else if ("4".equalsIgnoreCase(operate)) {
            op = ">=";
        } else if ("5".equalsIgnoreCase(operate)) {
            op = "<=";
        }
        return op;
    }

    /**
     * 获得条件脚本的SQL
     *
     * @param conditionField
     * @return
     */
    private List<SQLClause> getConditionList(String conditionField) {
        List<SQLClause> conditionFields = new ArrayList<>();
        if (StringUtil.isEmpty(conditionField)) {
            return conditionFields;
        }
        JSONArray jsonArray = JSONArray.fromObject(conditionField);

        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = (JSONObject) jsonArray.get(i);
            SQLClause field = new SQLClause();
            field.setJoinType("AND");
            field.setName(jsonObject.getString("na"));
            field.setComment(jsonObject.getString("cm"));
            field.setType(jsonObject.getString("ty"));
            field.setValue(jsonObject.get("va"));
            field.setValueFrom(jsonObject.getInt("vf"));
            field.setOperate(jsonObject.getString("op"));
            field.setControlType(jsonObject.getString("ct"));
            field.setQueryType(jsonObject.getString("qt"));
            conditionFields.add(field);
        }
        return conditionFields;
    }

    private Map<String, FormField> getBpmFormFieldMap(FormTable bpmFormTable) {
        Map<String, FormField> map = new HashMap<>(16);
        List<FormField> fieldList = bpmFormTable.getFieldList();
        if (BeanUtils.isEmpty(fieldList)) {
            return map;
        }
        for (FormField bpmFormField : fieldList) {
            String name = bpmFormField.getFieldName();
            map.put(name, bpmFormField);
        }
        return map;
    }

    /**
     * 获取过滤的SQL
     *
     * @param filterJson
     * @param tableMap    表对应的表Map<表，真实表名>
     * @param relationMap
     * @return
     */
    private String getFilterSQL(JSONObject filterJson,
                                Map<String, String> tableMap,
                                Map<String, Map<String, String>> relationMap) {
        if (JSONObjectUtil.isEmpty(filterJson)) {
            return "";
        }
        List<Map<String, Object>> operatorList = new ArrayList<>();
        // 转换成SQL
        List<FilterJsonStruct> filters = com.alibaba.fastjson.JSONArray
                .parseArray(filterJson.get("condition").toString(),
                        FilterJsonStruct.class);
        this.getFilterResult(filters, tableMap, relationMap, operatorList);

        return this.executeOperator(operatorList);
    }

    /**
     * 过滤所有的条件
     *
     * @param filters
     * @param tableMap
     * @param relationMap
     * @param operatorList
     */
    private void getFilterResult(List<FilterJsonStruct> filters,
                                 Map<String, String> tableMap,
                                 Map<String, Map<String, String>> relationMap,
                                 List<Map<String, Object>> operatorList) {
        for (FilterJsonStruct filter : filters) {
            // 组合条件
            if (filter.getBranch()) {
                List<Map<String, Object>> branchResultList = new ArrayList<Map<String, Object>>();
                this.getFilterResult(filter.getSub(), tableMap, relationMap,
                        branchResultList);
                String branchResult = this.executeOperator(branchResultList);
                Map<String, Object> resultMap = this.getResultMap(
                        filter.getCompType(), branchResult);
                operatorList.add(resultMap);
            } else {
                // 脚本直接返回结果
                if (filter.getRuleType().intValue() == 2) {
                    String script = filter.getScript();
                    if (StringUtil.isNotEmpty(script)) {
                        String tables = filter.getTables();

                        List<FilterJsonStruct> filterList = com.alibaba.fastjson.JSONArray
                                .parseArray(tables, FilterJsonStruct.class);
                        for (FilterJsonStruct filterJsonStruct : filterList) {
                            this.setTableMap(filterJsonStruct, tableMap,
                                    relationMap);
                        }

                        Map<String, Object> resultMap = this.getResultMap(
                                filter.getCompType(), script);
                        operatorList.add(resultMap);
                    }
                } else {
                    this.getNormalFilterResult(filter, tableMap, relationMap,
                            operatorList);
                }
            }
        }
    }

    /**
     * 获取普通条件的结果
     *
     * @param filter
     * @param tableMap
     * @param relationMap
     * @param operatorList
     */
    private void getNormalFilterResult(FilterJsonStruct filter,
                                       Map<String, String> tableMap,
                                       Map<String, Map<String, String>> relationMap,
                                       List<Map<String, Object>> operatorList) {
        String flowvarKey = filter.getFlowvarKey();
        String table = filter.getTable();
        String source = filter.getSource();

        // 获取表的数据
        this.setTableMap(filter, tableMap, relationMap);

        String script = "";
        switch (filter.getOptType()) {
            // 数字
            case 1:
                // 字符串
            case 2:
                // 条件一
                if (StringUtils.isNotEmpty(filter.getJudgeVal1())) {
                    script = this.getCompareScript(filter.getJudgeCon1(),
                            flowvarKey, filter.getJudgeVal1(), filter.getOptType(),
                            table, source, filter.getIsHidden());
                }
                // 条件二
                if (StringUtils.isNotEmpty(filter.getJudgeVal2())) {
                    String moreScript = getCompareScript(filter.getJudgeCon2(),
                            flowvarKey, filter.getJudgeVal2(), filter.getOptType(),
                            table, source, filter.getIsHidden());
                    if (StringUtils.isNotEmpty(script)) {
                        script = script + DataTemplate.CONDITION_AND;
                    }
                    script = script + moreScript;
                }
                break;
            // 日期
            case 3:
                // 条件一
                if (StringUtils.isNotEmpty(filter.getJudgeVal1())) {
                    String val1 = this.sqlToDate(filter.getJudgeVal1(),
                            filter.getDatefmt());
                    script = this.getCompareScript(filter.getJudgeCon1(),
                            flowvarKey, val1, filter.getOptType(), table, source,
                            filter.getIsHidden());
                }
                // 条件二
                if (StringUtils.isNotEmpty(filter.getJudgeVal2())) {
                    String val2 = this.sqlToDate(filter.getJudgeVal2(),
                            filter.getDatefmt());
                    String moreScript = getCompareScript(filter.getJudgeCon2(),
                            flowvarKey, val2, filter.getOptType(), table, source,
                            filter.getIsHidden());
                    if (StringUtils.isNotEmpty(script)) {
                        script = script + DataTemplate.CONDITION_AND;
                    }
                    script = script + moreScript;

                }
                break;
            // 字典
            case 4:
                String condition = filter.getJudgeVal1().replaceAll("&amp;&amp;", ",");
                script += getCompareScript(filter.getJudgeCon1(), flowvarKey,
                        condition, filter.getOptType(), table, source,
                        filter.getIsHidden());
                break;
            // 角色、组织、岗位选择器
            case 5:
                String judgeCon = filter.getJudgeCon1();
                String[] ids = filter.getJudgeVal1().split("&amp;&amp;");
                if (ids.length == 2) {
                    String newIds = "'" + ids[0] + "'";
                    String judgeVal = newIds.replaceAll(",", "',").replaceAll("',", "','");
                    script = getCompareScript(judgeCon, filter.getFlowvarKey(),
                            judgeVal, filter.getOptType(), table, source,
                            filter.getIsHidden());
                } else {// 特殊类型的
                    if ("3".equalsIgnoreCase(judgeCon)
                            || "4".equalsIgnoreCase(judgeCon)) {
                        script = getCompareScript(judgeCon, filter.getFlowvarKey(),
                                filter.getJudgeVal1(), filter.getOptType(), table,
                                source, filter.getIsHidden());
                    }

                }
                break;
            default:
                break;
        }

        if (StringUtil.isEmpty(script)) {
            return;
        }
        // 执行结果记录到operatorList中
        Map<String, Object> resultMap = this.getResultMap(filter.getCompType(),
                script);
        operatorList.add(resultMap);
    }

    /**
     * 获取根据条件组合的脚本
     *
     * @param judgeCon 判断条件
     * @param judgeVal 字段的值
     * @param judgeVal 字段的值
     * @param type     类型
     * @param table    表名
     * @param source   数据来源
     * @param isHidden
     * @return
     */
    private String getCompareScript(String judgeCon, String fieldName,
                                    String judgeVal, int type, String table, String source, int isHidden) {
        StringBuffer sb = new StringBuffer();
        fieldName = this.fixFieldName(fieldName, source, table);
        switch (type) {
            // 数值
            case 1:
                // 日期
            case 3:
                if ("1".equals(judgeCon)) {
                    sb.append(fieldName).append("=").append(judgeVal);
                } else if ("2".equals(judgeCon)) {
                    sb.append(fieldName).append("!=").append(judgeVal);
                } else if ("3".equals(judgeCon)) {
                    sb.append(fieldName).append(">").append(judgeVal);
                } else if ("4".equals(judgeCon)) {
                    sb.append(fieldName).append(">=").append(judgeVal);
                } else if ("5".equals(judgeCon)) {
                    sb.append(fieldName).append("<").append(judgeVal);
                } else if ("6".equals(judgeCon)) {
                    sb.append(fieldName).append("<=").append(judgeVal);
                }
                break;
            // 字符串
            case 2:
                // 字典
            case 4:
                if ("1".equals(judgeCon)) {
                    sb.append(fieldName).append("=").append("'").append(judgeVal)
                            .append("'");
                } else if ("2".equals(judgeCon)) {
                    sb.append(fieldName).append("!=").append("'").append(judgeVal)
                            .append("'");
                } else if ("3".equals(judgeCon)) {
                    sb.append("UPPER(").append(fieldName).append(")=")
                            .append(" UPPER('").append(judgeVal).append("')");
                } else if ("4".equals(judgeCon)) {
                    sb.append(fieldName).append(" LIKE").append(" '%")
                            .append(judgeVal).append("%'");
                } else if ("5".equals(judgeCon)) {
                    sb.append(fieldName).append(" LIKE").append(" '")
                            .append(judgeVal).append("%'");
                } else if ("6".equals(judgeCon)) {
                    sb.append(fieldName).append(" LIKE").append(" '%")
                            .append(judgeVal).append("'");
                }
                break;
            // 人员选择器
            case 5:
                if (isHidden == FormField.NO_HIDDEN) {
                    fieldName = fieldName + TableModel.PK_COLUMN_NAME;
                }
                if ("1".equals(judgeCon)) {
                    sb.append(fieldName).append(" in (")
                            .append(judgeVal).append(")");
                } else if ("2".equals(judgeCon)) {
                    sb.append(fieldName).append(" not in (")
                            .append(judgeVal).append(")");
                } else if ("3".equals(judgeCon)) {
                    sb.append(fieldName).append(" = :").append(judgeVal);
                } else if ("4".equals(judgeCon)) {
                    sb.append(fieldName).append(" != :").append(judgeVal);
                }
                break;
            default:
                break;
        }
        return sb.toString();
    }


    /**
     * @param operator 操作
     * @param result   给过
     * @return
     */
    private Map<String, Object> getResultMap(String operator, String result) {
        Map<String, Object> resultMap = new HashMap<>(2);
        resultMap.put("operator", operator);
        resultMap.put("result", result);
        return resultMap;
    }

    /**
     * 设置表的字段
     *
     * @param filter
     * @param tableMap
     * @param relationMap
     */
    private void setTableMap(FilterJsonStruct filter,
                             Map<String, String> tableMap,
                             Map<String, Map<String, String>> relationMap) {
        String table = filter.getTable();
        String source = filter.getSource();
        String mainTable = filter.getMainTable();
        String relation = filter.getRelation().toLowerCase();

        if (StringUtils.isNotEmpty(table)) {
            String tableLow = table.toLowerCase();
            if (!tableMap.containsKey(tableLow)) {
                tableMap.put(tableLow, this.fixTableName(table, source));
            }
            if (StringUtils.isNotEmpty(mainTable)) {
                String mainTableLow = mainTable.toLowerCase();
                if (!relationMap.containsKey(tableLow)
                        && !tableLow.equals(mainTableLow)) {
                    Map<String, String> map = new HashMap<>(16);
                    map.put(mainTableLow, relation);
                    relationMap.put(tableLow, map);
                }
            }
        }
    }

    /**
     * 获取SQL运算结果
     *
     * @param operatorList
     * @return
     */
    private String executeOperator(List<Map<String, Object>> operatorList) {
        if (operatorList.isEmpty()) {
            return "";
        }
        String returnVal = (String) operatorList.get(0).get("result");
        if (operatorList.size() == 1) {
            return returnVal;
        }
        int size = operatorList.size();
        for (int k = 1; k < size; k++) {
            Map<String, Object> resultMap = operatorList.get(k);
            String operator = resultMap.get("operator").toString();
            // 或运算
            if ("or".equals(operator)) {
                returnVal = "(" + returnVal + ") OR (" + resultMap.get("result") + ")";
                // 与运算
            } else if ("and".equals(operator)) {
                returnVal = "(" + returnVal + ") AND (" + resultMap.get("result") + ")";
            }
        }
        if (StringUtils.isNotEmpty(returnVal)) {
            returnVal = "(" + returnVal + ")";
        }
        return returnVal;
    }


    /**
     * 修正后的表名
     *
     * @param tableName
     * @param source
     * @return
     */
    private String fixTableName(String tableName, String source) {
        if (StringUtils.isEmpty(tableName) || StringUtils.isEmpty(source)) {
            return tableName;
        }
        if (DataTemplate.SOURCE_CUSTOM_TABLE.equals(source)) {
            tableName = TableModel.CUSTOMER_TABLE_PREFIX
                    + tableName.toUpperCase();
        }
        return tableName;

    }

    /**
     * 主表加密
     *
     * @param tableId
     * @param formData
     * @return
     * @throws Exception
     */
    public Map<String, Object> getEncryptMainFields(String tableId, FormData formData) throws Exception {
        //加密主表数据
        List<FormField> encryptList = formTableService.getEncryptFieldByTableId(tableId);
        Map<String, Object> mainFields = formData.getMainFields();
        encryptDecryptMap(mainFields, encryptList, FormField.ISENCRYPT, false);
        return mainFields;
    }

    /**
     * 子表加密
     *
     * @param tableId
     * @param formData
     * @throws Exception
     */
    public void getEncryptSubFields(String tableId, FormData formData) throws Exception {

        //查询所有子表的数据
        List<SubTable> subTables = formData.getSubTableList();
        if (subTables.size() > 0) {
            List<FormField> subEncryptList = getEncryptSubList(tableId);
            for (SubTable subTable : subTables) {
                List<Map<String, Object>> subTableDataList = subTable.getDataList();
                if (subTableDataList.size() > 0) {
                    decryptEncryptFileds(subTableDataList, subEncryptList, FormField.ISENCRYPT);
                }
            }
        }
    }

    /**
     * 删除数据
     *
     * @param alias 表单别名
     * @param pk    主键值
     */
    public void deleteData(String alias, String pk) throws Exception{
        FormDef bpmFormDef = formDefService.getDefaultPublishedByFormKey(alias);
        String tableId = bpmFormDef.getTableId();
        FormTable bpmFormTable = formTableService.getOneById(tableId);
        if (BeanUtils.isEmpty(bpmFormTable)) {
            return;
        }
        // 删除业务表数据	// 是本数据库
        FormTable table = new FormTable();
        table.setIsExternal(FormTable.NOT_EXTERNAL);
        this.deleteTable(DataTemplate.BUS_TABLE, bpmFormTable.getKeyDataType().shortValue() == FormTable.PKTYPE_STRING
                .shortValue() ? DataTemplate.BUS_TABLE_PK_STR
                : DataTemplate.BUS_TABLE_PK, pk,bpmFormTable.getDsAlias());
        String source = bpmFormTable.isExtTable() ? DataTemplate.SOURCE_OTHER_TABLE
                : DataTemplate.SOURCE_CUSTOM_TABLE;
        // 删除子表数据
        List<FormTable> subTableList = formTableService.getSubTableByMainTableId(tableId);

        for (FormTable subTable : subTableList) {
            this.deleteTable(this.fixTableName(subTable.getTableName(), source),
                    bpmFormTable.isExtTable() ? subTable.getRelation() : TableModel.FK_COLUMN_NAME,
                    subTable.getKeyDataType().shortValue() == FormTable.PKTYPE_STRING
                            .shortValue() ? pk : ("'" + pk + "'"),bpmFormTable.getDsAlias());
        }
        // 删除主表数据
        this.deleteTable(
                this.fixTableName(bpmFormTable.getTableName(), source),
                bpmFormTable.isExtTable() ? bpmFormTable.getPkField()
                        : TableModel.PK_COLUMN_NAME,
                bpmFormTable.getKeyDataType().shortValue() == FormTable.PKTYPE_STRING
                        .shortValue() ? pk : ("'" + pk + "'"),bpmFormTable.getDsAlias());

    }

    /**
     * 删除表的数据
     *
     * @param tableName 表名
     * @param pkField   主键
     * @param pk        主键值
     */
    public void deleteTable(String tableName, String pkField, String pk,String dsAlias) throws Exception{
        String delSQL = "DELETE FROM " + tableName + " WHERE  " + pkField + "= '" + pk + "'";
        JdbcTemplate jt = ServiceUtil.getJdbcTemplate(dsAlias);
        jt.execute(delSQL);
    }


    /**
     * 获取表单数据
     *
     * @param alias
     * @param pk
     * @return
     * @throws Exception
     */
    public Map<String, Object> getForm(String alias, String pk) throws Exception {
        ProcessRun processRun = processRunService.getByBusinessKey(pk);
        List<FormDef> bpmFormDefList = formDefDao.getByFormKeyIsDefault(
                alias, FormDef.IS_DEFAULT);
        if (BeanUtils.isEmpty(bpmFormDefList)) {
            return null;
        }
        FormDef bpmFormDef = bpmFormDefList.get(0);
        return formHandlerService.getFormDetail(bpmFormDef, pk, processRun, "", false);
    }

    /**
     * 导出execl
     *
     * @param alias
     * @param exportIds
     * @param exportType 导出的类型
     * @param params     数据
     * @return
     * @throws Exception
     */
    public HSSFWorkbook export(String alias, String[] exportIds, int exportType, Map<String, Object> params) throws Exception {
        String curUserId = ContextUtil.getCurrentUserId();
        String curOrgId = ContextUtil.getCurrentOrgId();
        CommonVar.setCurrentVars(params);

        DataTemplate dataTemplate = dataTemplateDao.getByFormKey(alias);
        if (BeanUtils.isEmpty(dataTemplate)) {
            dataTemplate = dataTemplateDao.getById(alias);
        }
        if (BeanUtils.isEmpty(dataTemplate)) {
            return null;
        }
        FormTable formTable = formTableService.getByTableId(
                dataTemplate.getTableId(), FormField.FIELD_NORMAL_HIDDEN);
        // 是否有子表
        Boolean isSubTable = formTable.getSubTableList().size() > 0;

        // 获取权限map
        Map<String, Object> rightMap = this.getRightMap(curUserId, curOrgId);

        Map<String, Boolean> exportFieldMap = this.getFieldPermission(dataTemplate.getExportField(), rightMap);
        // 显示的列注释
        Map<String, List<String>> displayFieldDesc = this
                .getDisplayFieldList(dataTemplate.getExportField(),
                        exportFieldMap, 0, isSubTable);

        // 显示的字段的名
        Map<String, List<String>> displayFieldName = this
                .getDisplayFieldList(dataTemplate.getExportField(),
                        exportFieldMap, 1, isSubTable);

        // 获取表
        Map<String, String> tableNameMap = this.getTableName(formTable);
        // 格式化的数据
        Map<String, Object> formatData = this.getFormatDataMap(formTable, dataTemplate.getFormKey());

        // 如果有选择数据, 设置为不需要分页,导出所有的
        if (exportType == 0) {
            dataTemplate.setNeedPage((short) 0);
        }
        // 只有
        if (exportType != 1) {
            exportIds = null;
        }
        // 初始化对对当前用户有过滤效果的过滤条件，把filterField的内容重新赋值，只保留对当前用户有过滤效果的内容
        dataTemplate = this.getRightFilterField(dataTemplate, rightMap, "", alias);
        // 取得当前过滤的条件
        JSONObject filterJson = this.getFilterJson(
                dataTemplate.getFilterField(), params);
        // 主表显示的数据
        dataTemplate = this.getData(dataTemplate, formTable, rightMap,
                params, null, formatData, filterJson);

        // 主表数据和从表的数据
        Map<String, List<List<Object>>> dataMapList = this.getDataMapList(
                dataTemplate, displayFieldName, exportFieldMap,
                formTable, exportIds);

        // 以下开始写excel
        // 创建新的Excel 工作簿
        Excel excel = new Excel();

        int j = 0;
        for (Iterator<Map.Entry<String, List<String>>> it = displayFieldDesc.entrySet().iterator(); it.hasNext(); ) {
            Map.Entry<String, List<String>> e = (Map.Entry<String, List<String>>) it
                    .next();
            String key = e.getKey();
            String tableDesc = tableNameMap.get(key);
            List<String> list = e.getValue();
            if (j == 0) {
                // 重命名当前处于工作状态的表的名称
                excel.sheet().sheetName(tableDesc);
            } else {
                // 把子表激活
                excel.setWorkingSheet(j).sheetName(tableDesc);
            }
            // 第一行标题 加粗
            excel.row(0, 0).value(list.toArray()).font(new IFontEditor() {
                // 设置字体
                @Override
                public void updateFont(Font font) {
                    font.boldweight(BoldWeight.BOLD);// 粗体
                }
            }).bgColor(Color.GREY_25_PERCENT);
            // 取得表的数据
            List<List<Object>> dataList = dataMapList.get(key);

            if (BeanUtils.isNotEmpty(dataList)) {
                // 从第2行写入数据
                for (int i = 0; i < dataList.size(); i++) {
                    List<Object> objectList = dataList.get(i);
                    excel.row(i + 1).value(objectList.toArray())
                            .dataFormat("@");
                }
            }
            j++;
        }

        return excel.getWorkBook();
    }

    /**
     * 获取主键、外键和表属性对应权限
     *
     * @param fieldJson
     * @param rightMap
     * @return
     */
    private Map<String, Boolean> getFieldPermission(String fieldJson, Map<String, Object> rightMap) {
        JSONArray jsonAry = JSONArray.fromObject(fieldJson);
        Map<String, Boolean> map = new ListOrderedMap();
        if (JSONObjectUtil.isEmpty(jsonAry)) {
            return map;
        }
        for (Object obj1 : jsonAry) {
            JSONObject json1 = JSONObject.fromObject(obj1);
            JSONArray fields = (JSONArray) json1.get("fields");
            String tableName = (String) json1.get("tableName");
            String isMain = json1.get("isMain").toString();

            // 添加主键字段(每个表都添加)
            String key = tableName + TableModel.PK_COLUMN_NAME;
            map.put(key, true);

            // 添加外键字段(子表才有)
            if (!"1".equals(isMain)) {
                String fkKey = tableName + TableModel.FK_COLUMN_NAME;
                map.put(fkKey, true);
            }

            for (Object obj : fields) {
                JSONObject json = JSONObject.fromObject(obj);
                String name = (String) json.get("name");
                JSONArray rights = (JSONArray) json.get("right");
                for (Object right : rights) {
                    JSONObject rightJson = JSONObject.fromObject(right);
                    map.put(tableName + name, QueryUtil.hasRight(rightJson, rightMap));
                }
            }

        }
        return map;
    }

    /**
     * 获得显示的字段
     *
     * @param displayField    展示字段
     * @param displayFieldMap 所有字段的map，是否展示为true or false
     * @param b               0代表显示desc 其它代表显示name
     * @param isSubTable      是否属于子表
     * @return map<表名字段列表>
     */
    @SuppressWarnings("unchecked")
    private Map<String, List<String>> getDisplayFieldList(String displayField,
                                                          Map<String, Boolean> displayFieldMap, int b, Boolean isSubTable) {
        Map<String, List<String>> displayFieldList = new ListOrderedMap();
        JSONArray jsonAry = JSONArray.fromObject(displayField);
        if (JSONObjectUtil.isEmpty(jsonAry) || BeanUtils.isEmpty(displayFieldMap)) {
            return displayFieldList;
        }

        for (Object obj1 : jsonAry) {
            List<String> list = new ArrayList<>(16);
            JSONObject json1 = JSONObject.fromObject(obj1);
            String table = (String) json1.get("tableName");
            String isMain = json1.get("isMain").toString();
            JSONArray fields = (JSONArray) json1.get("fields");

            //不管是主表还是子表都加入主键
            String pkVal = getFk("1", b);
            list.add(pkVal);

            //如果有子表再加入外键
            if (isSubTable && !"1".equals(isMain)) {
                String val = getFk(isMain, b);
                list.add(val);
            }

            for (Object obj : fields) {
                JSONObject json = JSONObject.fromObject(obj);
                String name = (String) json.get("name");
                String desc = (String) json.get("desc");
                String tableName = (String) json.get("tableName");
                Boolean f = displayFieldMap.get(tableName + name);
                if (!f) {
                    continue;
                }
                if (b == 0) {
                    list.add(desc);
                } else {
                    list.add(name);
                }
            }
            displayFieldList.put(table, list);
        }
        return displayFieldList;
    }

    /**
     * 根据isMain与字段参数获取值
     * 字段参数b 值为0代表注释desc，1代表name
     *
     * @param isMain isMain
     * @param b      字段参数
     * @return val
     */
    private String getFk(String isMain, int b) {
        String val;
        if (FormTable.IS_MAIN == Integer.parseInt(isMain)) {
            if (b == FormTable.IS_NOT_MAIN) {
                val = "主键";
            } else {
                val = TableModel.PK_COLUMN_NAME;
            }
        } else {
            if (b == FormTable.IS_NOT_MAIN) {
                val = "外键";
            } else {
                val = TableModel.FK_COLUMN_NAME;
            }
        }
        return val;
    }

    /**
     * 获取表名与表注释（包含子表）
     *
     * @param formTable formTable
     * @return map
     */
    private Map<String, String> getTableName(FormTable formTable) {
        Map<String, String> map = new HashMap<>(16);
        map.put(formTable.getTableName(), formTable.getTableDesc());
        for (FormTable table : formTable.getSubTableList()) {
            map.put(table.getTableName(), table.getTableDesc());
        }
        return map;
    }

    /**
     * 获取主表与子表数据，并按字段顺序存入list
     *
     * @param dataTemplate
     * @param displayFieldName
     * @param displayFieldMap
     * @param formTable
     * @param exportIds
     * @return
     * @throws Exception
     */
    private Map<String, List<List<Object>>> getDataMapList(
            DataTemplate dataTemplate,
            Map<String, List<String>> displayFieldName,
            Map<String, Boolean> displayFieldMap, FormTable formTable,
            String[] exportIds) throws Exception {
        Map<String, List<List<Object>>> dataObjectList = new HashMap<>(16);
        // 主表的数据
        List<Map<String, Object>> list = dataTemplate.getList();

        //解密主表的数据
        List<FormField> encryptList = formTableService.getEncryptFieldByTableId(dataTemplate.getTableId());
        decryptEncryptFileds(list, encryptList, FormField.ISENCRYPT_NOT);


        if (BeanUtils.isEmpty(displayFieldName) || BeanUtils.isEmpty(list)) {
            return dataObjectList;
        }
        String mainTableName = formTable.getTableName();

        List<List<Object>> dataMainList = new ArrayList<>(16);
        String pkField = formTable.getPkField();
        Short keyDataType = formTable.getKeyDataType();

        List<Object> keyValList = new ArrayList<>(16);

        for (Map<String, Object> map : list) {
            List<Object> dataList = new ArrayList<>(16);
            List<String> displayFieldList = displayFieldName.get(mainTableName);
            for (String name : displayFieldList) {
                // 按字段的顺序
                Boolean f = displayFieldMap.get(mainTableName + name);
                if (f) {
                    dataList.add(map.get(name));
                }
            }
            // 导出指定的ID
            if (ArrayUtils.isNotEmpty(exportIds)) {
                dataList = getExportDataList(dataList, map, exportIds, pkField);
            }

            if (BeanUtils.isNotEmpty(dataList)) {
                dataMainList.add(dataList);
                keyValList.add(map.get(pkField));
            }
        }
        // 主表的数据
        dataObjectList.put(mainTableName, dataMainList);
        if (formTable.getSubTableList().size() == 0) {
            return dataObjectList;
        }

        DbContextHolder.setDataSource(formTable.getDsAlias());
        // 子表数据
        for (FormTable subTable : formTable.getSubTableList()) {
            List<List<Object>> dataSubList = new ArrayList<>(16);
            String subTableName = subTable.getTableName();
            List<String> displayFieldList = displayFieldName.get(subTableName);
            Map<String, Object> subFormatData = getFormatDataMap(subTable, dataTemplate.getFormKey());
            for (Object o : keyValList) {
                // 子表数据
                dataSubList = this.getSubTableDataList(dataSubList,
                        displayFieldList, displayFieldMap, o,
                        keyDataType, subTable, subTableName,
                        subFormatData, formTable.getTableId());
            }
            dataObjectList.put(subTable.getTableName(), dataSubList);
        }

        return dataObjectList;
    }

    /**
     * 获得导出的数据list
     *
     * @param dataList
     * @param map
     * @param ids      exportIds
     * @param pkField
     * @return
     */
    private List<Object> getExportDataList(List<Object> dataList, Map<String, Object> map, String[] ids, String pkField) {
        Object idVal = map.get(pkField);
        if (idVal == null) {
            return Collections.emptyList();
        }
        String idStr = String.valueOf(idVal);
        Boolean b = ArrayUtils.contains(ids, idStr);
        if (!b) {
            dataList = null;
        }
        return dataList;
    }

    /**
     * 获取子表的数据
     *
     * @param dataSubList
     * @param displayFieldList
     * @param displayFieldMap
     * @param o
     * @param keyDataType
     * @param subTable
     * @param subTableName
     * @param tableId
     * @param formatData
     * @return
     * @throws Exception
     */
    private List<List<Object>> getSubTableDataList(
            List<List<Object>> dataSubList, List<String> displayFieldList,
            Map<String, Boolean> displayFieldMap, Object o,
            Short keyDataType, FormTable subTable, String subTableName,
            Map<String, Object> formatData, String tableId)
            throws Exception {
        String idStr = String.valueOf(o);
        String relation = StringUtils.isEmpty(subTable.getRelation()) ? TableModel.FK_COLUMN_NAME
                : subTable.getRelation();
        StringBuffer sql = new StringBuffer("SELECT * FROM ");
        sql.append(subTable.getFactTableName()).append(" WHERE ")
                .append(relation).append(" = ");
        if (keyDataType.shortValue() == FormTable.PKTYPE_NUMBER.shortValue()) {
            sql.append(Long.parseLong(idStr));
        } else {
            sql.append("'" + idStr + "'");
        }
        List<Map<String, Object>> list = jdbcTemplate.queryForList(
                sql.toString());
        // 转换的数据
        if (BeanUtils.isEmpty(list)) {
            return dataSubList;
        }
        // 整理数据
        list = this.getDataList(list, subTable, formatData, false);

        //解密子表数据
        List<FormField> subEncryptList = getEncryptSubList(tableId);
        if (list.size() > 0) {
            decryptEncryptFileds(list, subEncryptList, FormField.ISENCRYPT_NOT);
        }

        for (Map<String, Object> map : list) {
            List<Object> dataList = new ArrayList<>(16);
            for (String name : displayFieldList) {
                // 按字段的顺序取出数据
                Boolean f = displayFieldMap.get(subTableName + name);
                if (!f) {
                    continue;
                }
                if (relation.equals(name)) {
                    dataList.add(map.get(name));
                } else {
                    dataList.add(map.get(name));
                }
            }
            dataSubList.add(dataList);
        }
        return dataSubList;
    }

    /**
     * 导入文件
     *
     * @param inputStream
     * @param alias
     * @throws Exception
     */
    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> importFile(InputStream inputStream, String alias, String importType) throws Exception {
        ExcelReader excel = new ExcelReader();
        // 读出execl
        TableEntity tableEntity = excel.readFile(inputStream);
        DataTemplate dataTemplate = dataTemplateDao.getByFormKey(alias);

        //在线表单导入数据，表中的租户id，默认为当前用户的租户id

        String tenantId = ContextUtil.getCurrentUserTenantId();
        List<DataEntity> dataEntityList = tableEntity.getDataEntityList();
        for (DataEntity dataEntity : dataEntityList) {
            FieldEntity fieldEntity = new FieldEntity();
            //bpm_form_field表中的字段名称
            fieldEntity.setName(FormConstants.STR_TENANTID);
            fieldEntity.setValue(String.valueOf(tenantId));
            dataEntity.getFieldEntityList().add(fieldEntity);
        }
        //
        FormTable formTable = formTableService.getByTableId(
                dataTemplate.getTableId(), FormField.FIELD_NORMAL_HIDDEN);
        Map<String, Object> resultMap = new HashMap<>(16);
        resultMap.put("tableEntity", tableEntity);
        resultMap.put("formTable", formTable);
        for (DataEntity dataEntity : tableEntity.getDataEntityList()) {
            String pkValue = dataEntity.getPkVal();
            Map<String, List<DataEntity>> subDataEntityMap = getSubDataEntityMap(
                    tableEntity, pkValue);
            FormData formData = parseFormData(dataEntity,
                    subDataEntityMap, formTable, importType);
            formHandlerService.handFormData(formData, alias, null);
        }
        return resultMap;
    }

    /**
     * 获取主表对应子表数据，按顺序存放到map中
     *
     * @param tableEntity
     * @param pkValue
     * @return
     */
    private Map<String, List<DataEntity>> getSubDataEntityMap(TableEntity tableEntity, String pkValue) {
        Map<String, List<DataEntity>> subDataEntityMap = new ListOrderedMap();
        if (tableEntity.getSubTableEntityList() == null) {
            return null;
        }
        // 子表
        for (TableEntity subTableEntity : tableEntity.getSubTableEntityList()) {
            if (subTableEntity == null) {
                continue;
            }
            String key = subTableEntity.getName();
            List<DataEntity> subDataEntityList = new ArrayList<>(16);
            for (DataEntity subDataEntity : subTableEntity.getDataEntityList()) {
                List<FieldEntity> fieldEntityList = subDataEntity.getFieldEntityList();
                //获取文件第两列即第二个字段的内容（是子表的外键字段）,如果外键字段的值和主表的主键一致就加入到列表
                FieldEntity fieldEntity = fieldEntityList.get(1);
                if (fieldEntity.getValue().equals(pkValue)) {
                    subDataEntityList.add(subDataEntity);
                }
            }
            subDataEntityMap.put(key, subDataEntityList);
        }
        return subDataEntityMap;
    }

    /**
     * 转换表的数据
     *
     * @param dataEntity
     * @param subDataEntityMap
     * @param formTable
     * @return
     * @throws Exception
     */
    private FormData parseFormData(DataEntity dataEntity,
                                   Map<String, List<DataEntity>> subDataEntityMap,
                                   FormTable formTable, String importType) throws Exception {
        FormData formData = new FormData(formTable);
        PkValue pkValue = FormDataUtil.generatePk(formTable);
        //如果是更新的情况，就直接用以前的ID
        if ("update".equals(importType)) {
            String id = dataEntity.getFieldEntityList().get(0).getValue();
            if (StringUtil.isNotEmpty(id)) {
                pkValue.setIsAdd(false);
                pkValue.setValue(id);
            }
        }
        formData.setPkValue(pkValue);
        // 处理主表
        handleMain(dataEntity, formData);
        // 处理子表
        handSub(subDataEntityMap, formData, importType);
        return formData;
    }

    /**
     * 处理主表
     *
     * @param dataEntity
     * @param formData
     */
    private void handleMain(DataEntity dataEntity, FormData formData) {
        // 主表 main
        FormTable mainTableDef = formData.getFormTable();
        List<FormField> mainFields = mainTableDef.getFieldList();
        // 主表字段
        Map<String, FormField> mainFieldDescMap = this
                .convertFieldToMap(mainFields);

        // 将主表JSON转换成map数据。
        Map<String, Object> mainFiledsData = handleRow(mainTableDef,
                mainFieldDescMap, dataEntity.getFieldEntityList());
        // 添加主表数据
        formData.addMainFields(mainFiledsData);

        PkValue pkValue = formData.getPkValue();

        formData.addMainFields(pkValue.getName().toLowerCase(),
                pkValue.getValue());
    }

    /**
     * 处理子表
     *
     * @param importType
     * @param subDataEntityMap
     * @param formData
     * @throws Exception
     */
    private void handSub(Map<String, List<DataEntity>> subDataEntityMap,
                         FormData formData, String importType) throws Exception {
        FormTable mainTable = formData.getFormTable();
        List<FormTable> listTable = mainTable.getSubTableList();
        // 将表名消息并作为键和表对象进行关联。
        Map<String, FormTable> formTableMap = convertTableMap(listTable);

        boolean isExternal = mainTable.isExtTable();
        if (subDataEntityMap == null) {
            return;
        }
        for (Iterator<Map.Entry<String, List<DataEntity>>> it = subDataEntityMap
                .entrySet().iterator(); it.hasNext(); ) {
            SubTable subTable = new SubTable();
            Map.Entry<String, List<DataEntity>> e = (Map.Entry<String, List<DataEntity>>) it
                    .next();
            String key = e.getKey();
            List<DataEntity> dataEntityList = e.getValue();
            FormTable subFormTable = formTableMap.get(key);

            // 获取子表的列元数据。
            List<FormField> subTableFields = subFormTable.getFieldList();

            //修改导入的情况要加入主键
            if ("update".equals(importType)) {
                FormField field = new FormField();
                field.setFieldName(TableModel.PK_COLUMN_NAME);
                field.setFieldDesc("主键");
                subTableFields.add(field);
            }

            // 将子表的字段名称作为键，字段对象作为值放到map对象当中。
            Map<String, FormField> subFieldDescMap = convertFieldToMap(subTableFields);
            // 设置子表名称
            subTable.setTableName(subFormTable.getTableName());

            // 设置子表的主键和外键名称。
            String pk = subFormTable.getPkField();
            if (isExternal) {
                subTable.setPkName(pk);
                subTable.setFkName(subFormTable.getRelation());
            } else {
                subTable.setPkName(TableModel.PK_COLUMN_NAME);
                subTable.setFkName(TableModel.FK_COLUMN_NAME);
            }

            for (DataEntity dataEntity : dataEntityList) {
                Map<String, Object> subRow = handleRow(subFormTable,
                        subFieldDescMap, dataEntity.getFieldEntityList());
                // 处理主键数据
                handFkRow(subFormTable, subRow, formData.getPkValue());
                // 处理需要计算的数据。
                subTable.addRow(subRow);
            }

            formData.addSubTable(subTable);
        }
    }

    /**
     * 转换Map<字段注释,自定义表>
     *
     * @param list
     * @return
     */
    private Map<String, FormField> convertFieldToMap(List<FormField> list) {
        Map<String, FormField> map = new HashMap<>(16);
        for (Iterator<FormField> it = list.iterator(); it.hasNext(); ) {
            FormField field = it.next();
            map.put(field.getFieldDesc(), field);
            if(FormConstants.FIELD_TENANTID.equalsIgnoreCase(field.getFieldDesc())){
                map.put(FormConstants.STR_TENANTID, field);
            }
        }
        return map;
    }

    private Map<String, FormTable> convertTableMap(List<FormTable> list) {
        Map<String, FormTable> map = new HashMap<>(16);
        for (FormTable tb : list) {
            map.put(tb.getTableDesc(), tb);
        }
        return map;
    }

    /**
     * 处理一行数据
     *
     * @param formTable
     * @param fieldDescMap
     * @param list
     * @return
     */
    private Map<String, Object> handleRow(FormTable formTable,
                                          Map<String, FormField> fieldDescMap, List<FieldEntity> list) {
        boolean isExternal = formTable.isExtTable();
        // int keyType= bpmFormTable.getKeyDataType();
        // String pkField=bpmFormTable.getPkField();
        String colPrefix = "";
        Map<String, Object> row = new HashMap<>(16);
        // 对字段名称进行遍历
        for (FieldEntity fieldEntity : list) {
            String name = fieldEntity.getName();
            String value = fieldEntity.getValue();
            FormField formField = fieldDescMap.get(name);
            if (BeanUtils.isEmpty(formField)) {
                continue;
            }
            String key = formField.getFieldName();
            Object convertValue = FormDataUtil.convertType(value, formField);
            String fieldName = key.toLowerCase();
            if (!isExternal
                    && !fieldName.equalsIgnoreCase(TableModel.PK_COLUMN_NAME)) {
                fieldName = (colPrefix + key).toLowerCase();
            }
            row.put(fieldName, convertValue);
        }
        return row;
    }

    /**
     * 处理子表行数据的主键和外键。
     *
     * <pre>
     * 添加子表的主键和外键。
     * </pre>
     *
     * @param formTable 子表定义。
     * @param rowData   子表一行数据。
     * @param pkValue   主键数据。
     * @throws Exception
     */
    public static void handFkRow(FormTable formTable,
                                 Map<String, Object> rowData, PkValue pkValue) throws Exception {
        boolean isExternal = formTable.isExtTable();
        // 外部表数据
        if (isExternal) {
            String pkField = formTable.getPkField().toLowerCase();
            if (!rowData.containsKey(pkField)) {
                PkValue pk = FormDataUtil.generatePk(formTable);
                rowData.put(pk.getName(), pk.getValue());
            } else {
                Object obj = rowData.get(pkField);
                if (obj == null || "".equals(obj.toString().trim())) {
                    PkValue pk = FormDataUtil.generatePk(formTable);
                    rowData.put(pk.getName(), pk.getValue());
                }
            }
            String fk = formTable.getRelation();
            rowData.put(fk, pkValue.getValue());
        }
        // 本地表数据
        else {
            String pkField = formTable.getPkField().toLowerCase();
            // 没有包含主键则添加一个。
            if (!rowData.containsKey(pkField)) {
                String pk =  UUIDUtils.getUUIDFor32();
                rowData.put(TableModel.PK_COLUMN_NAME.toLowerCase(), pk);
            } else {
                String id = (String) rowData.get(pkField);
                if (StringUtil.isEmpty(id)) {
                    String pk = UUIDUtils.getUUIDFor32();
                    rowData.put(TableModel.PK_COLUMN_NAME.toLowerCase(), pk);
                }
            }

            rowData.put(TableModel.FK_COLUMN_NAME.toLowerCase(),
                    pkValue.getValue());
        }

    }

    @Transactional(rollbackFor = Exception.class)
    public void batchDeleteData(String alias, String pks)throws Exception {
        String[] aryPks = pks.split(",");
        for (String pk : aryPks) {
            deleteData(alias, pk);
        }

    }
}